SQL Server JSON: A Complete Guide to Working with JSON Data in SQL Server

Modern applications exchange data in flexible formats, and JSON (JavaScript Object Notation) has become the standard for APIs, web applications, and microservices. To support modern data workflows, Microsoft SQL Server includes powerful JSON capabilities that allow developers to store, query, parse, and generate JSON directly inside SQL queries.

In this blog, we’ll explore how SQL Server handles JSON data, key JSON functions, practical examples, performance considerations, and best practices.

What is JSON?

JSON is a lightweight data format used for data exchange between applications and servers.

Example JSON:

{
  "id": 101,
  "name": "John Doe",
  "city": "Lucknow",
  "skills": ["SQL", "Python", "Azure"]
}

Unlike XML, JSON is compact, easy to read, and widely used in REST APIs and cloud applications.

JSON Support in SQL Server

Starting with SQL Server 2016, Microsoft introduced built-in JSON support.

SQL Server does not provide a native JSON data type like PostgreSQL’s JSONB. Instead, JSON data is stored as:

NVARCHAR

However, SQL Server includes functions to:

• Validate JSON
• Extract values
• Modify JSON
• Convert relational data into JSON
• Parse JSON into table format

Why Use JSON in SQL Server?

Using JSON in SQL Server provides several advantages:

1. API Integration

Most web APIs return JSON responses.

2. Flexible Schema

JSON allows semi-structured data storage.

3. Easier Data Exchange

Applications can directly consume JSON data.

4. Reduced Complexity

Nested objects reduce table joins in some scenarios.

JSON Functions in SQL Server

Here are the major JSON functions available in SQL Server.

1. ISJSON() Function

The ISJSON() function validates JSON format.

Example

DECLARE @data NVARCHAR(MAX)

SET @data = '{"name":"John","age":30}'

SELECT ISJSON(@data) AS Result

Output

1

1 means valid JSON.

2. JSON_VALUE() Function

JSON_VALUE() extracts a single scalar value from JSON.

Example

DECLARE @emp NVARCHAR(MAX)

SET @emp = '
{
  "name":"Alice",
  "department":"IT",
  "salary":70000
}'

SELECT JSON_VALUE(@emp, '$.department') AS Department

Output

IT

3. JSON_QUERY() Function

Use JSON_QUERY() when extracting arrays or objects.

Example

DECLARE @json NVARCHAR(MAX)

SET @json = '
{
  "employee":"David",
  "skills":["SQL","Azure","Power BI"]
}'

SELECT JSON_QUERY(@json, '$.skills') AS Skills

Output

["SQL","Azure","Power BI"]

4. JSON_MODIFY() Function

JSON_MODIFY() updates JSON data.

Example

DECLARE @json NVARCHAR(MAX)

SET @json = '
{
  "name":"Tom",
  "city":"Delhi"
}'

SET @json = JSON_MODIFY(@json, '$.city', 'Mumbai')

SELECT @json AS UpdatedJSON

Output

{
  "name":"Tom",
  "city":"Mumbai"
}

5. OPENJSON() Function

OPENJSON() converts JSON into relational table format.

This is one of the most powerful JSON features in SQL Server.

Example

DECLARE @json NVARCHAR(MAX)

SET @json = '
[
  {"id":1,"name":"Amit"},
  {"id":2,"name":"Riya"}
]'

SELECT *
FROM OPENJSON(@json)
WITH (
    id INT,
    name NVARCHAR(100)
)

Output

6. FOR JSON Clause

FOR JSON converts SQL query results into JSON.

Example

SELECT
    EmployeeID,
    FirstName,
    LastName
FROM Employees
FOR JSON AUTO

Output

[
  {
    "EmployeeID":1,
    "FirstName":"John",
    "LastName":"Smith"
  }
]

FOR JSON AUTO vs FOR JSON PATH

FOR JSON AUTO

Automatically formats JSON based on table structure.

SELECT * FROM Employees
FOR JSON AUTO

FOR JSON PATH

Provides complete control over JSON formatting.

SELECT
    EmployeeID AS 'employee.id',
    FirstName AS 'employee.name'
FROM Employees
FOR JSON PATH

Working with Nested JSON

SQL Server can also handle nested JSON structures.

Example

SELECT
    101 AS 'employee.id',
    'Rahul' AS 'employee.name',
    'IT' AS 'employee.department'
FOR JSON PATH

Output

[
  {
    "employee": {
      "id": 101,
      "name": "Rahul",
      "department": "IT"
    }
  }
]

Parsing Complex JSON with OPENJSON

You can parse nested arrays using CROSS APPLY.

Example

DECLARE @json NVARCHAR(MAX)

SET @json = '
{
  "orders":[
    {
      "id":1,
      "items":["Laptop","Mouse"]
    }
  ]
}'

SELECT *
FROM OPENJSON(@json, '$.orders')
WITH (
    id INT,
    items NVARCHAR(MAX) AS JSON
)

Indexing JSON Data in SQL Server

Since JSON is stored as NVARCHAR, indexing directly is not possible.

Instead:

• Create computed columns
• Extract JSON values
• Add indexes to computed columns

Example

ALTER TABLE Employees
ADD Department AS JSON_VALUE(EmployeeJSON, '$.department')

CREATE INDEX IX_Department
ON Employees(Department)

This improves query performance significantly.

SQL Server JSON vs XML

JSON is generally preferred for modern applications.

Common Errors

Invalid JSON Format

SELECT ISJSON('{name:John}')

Returns:

0

Because property names require quotes.

Incorrect JSON Path

SELECT JSON_VALUE(@json, '$.wrongkey')

Returns NULL if the key does not exist.

Future of JSON in SQL Server

JSON adoption continues to grow with cloud-native applications, APIs, and data integration pipelines. SQL Server’s JSON support makes it easier for developers to combine relational and semi-structured data without needing separate NoSQL databases.

With the rise of microservices and event-driven architectures, JSON handling in databases will remain a critical skill for developers and database administrators.

Conclusion

SQL Server JSON functionality enables developers to work efficiently with modern semi-structured data while still leveraging the power of relational databases.

Key takeaways:

• SQL Server supports JSON through built-in functions
• JSON data is stored using NVARCHAR
• OPENJSON is extremely powerful for parsing
• FOR JSON helps generate API-ready responses
• Performance improves with computed columns and indexing

Whether you're building APIs, integrating cloud services, or managing dynamic application data, mastering SQL Server JSON can significantly improve your database solutions.