SQL Server Parse JSON: OPENJSON, JSON_VALUE and JSON_QUERY

Need to parse JSON in SQL Server? SQL Server provides built-in JSON functions that let you extract values, turn JSON arrays into rows and columns, query nested objects, validate JSON, and modify JSON without an external parser. The main functions are OPENJSON, JSON_VALUE, JSON_QUERY, ISJSON, and JSON_MODIFY.

Quick answer: Use JSON_VALUE() to extract one scalar value, JSON_QUERY() to return an object or array, and OPENJSON() when you need to parse a JSON object or array into rows and columns. JSON support is available in SQL Server 2016 and later, with OPENJSON requiring database compatibility level 130 or higher.

SQL Server JSON functions at a glance

How to parse JSON in SQL Server

1. Parse a JSON array with OPENJSON

OPENJSON() is the main function to use when you need to transform JSON into a relational result set. It is especially useful for JSON arrays containing multiple objects.

DECLARE @json NVARCHAR(MAX) =
N'{
    "employees": [
        {"id": 1, "name": "John Doe", "role": "Manager"},
        {"id": 2, "name": "Jane Smith", "role": "Developer"}
    ]
}';

SELECT id, name, role
FROM OPENJSON(@json, '$.employees')
WITH (
    id INT '$.id',
    name NVARCHAR(50) '$.name',
    role NVARCHAR(50) '$.role'
);

The $.employees path tells OPENJSON to parse the employees array. The WITH clause defines the columns and their JSON paths.

2. Extract a value with JSON_VALUE

Use JSON_VALUE() when you need one scalar value such as a name, ID, status, date, or number.

SELECT JSON_VALUE(@json, '$.employees[0].name') AS EmployeeName;

This returns the scalar value from the specified JSON path. JSON_VALUE is also useful when filtering or sorting table data stored in a JSON column.

3. Extract an object or array with JSON_QUERY

Use JSON_QUERY() when the value you need is a JSON object or array rather than a scalar value.

SELECT JSON_QUERY(@json, '$.employees') AS Employees;

Parse JSON stored in a SQL Server table

JSON is commonly stored in an NVARCHAR column. You can extract individual properties directly from that column with JSON_VALUE, or use OPENJSON when the column contains arrays or more complex structures.

CREATE TABLE EmployeeData (
    Id INT IDENTITY PRIMARY KEY,
    EmployeeJSON NVARCHAR(MAX)
);

INSERT INTO EmployeeData (EmployeeJSON)
VALUES
(N'{"id":1,"name":"John Doe","role":"Manager"}'),
(N'{"id":2,"name":"Jane Smith","role":"Developer"}');

SELECT
    JSON_VALUE(EmployeeJSON, '$.id') AS EmployeeId,
    JSON_VALUE(EmployeeJSON, '$.name') AS EmployeeName,
    JSON_VALUE(EmployeeJSON, '$.role') AS EmployeeRole
FROM EmployeeData;

Parse nested JSON with OPENJSON

OPENJSON can navigate nested objects and arrays by using JSON paths in the WITH clause. This makes it useful for flattening API responses and other hierarchical JSON into relational columns.

DECLARE @complexJson NVARCHAR(MAX) =
N'{
    "company": {
        "name": "TechCorp",
        "employees": [
            {
                "id": 1,
                "name": "John Doe",
                "details": {
                    "role": "Manager",
                    "salary": 75000
                }
            },
            {
                "id": 2,
                "name": "Jane Smith",
                "details": {
                    "role": "Developer",
                    "salary": 60000
                }
            }
        ]
    }
}';

SELECT id, name, role, salary
FROM OPENJSON(@complexJson, '$.company.employees')
WITH (
    id INT '$.id',
    name NVARCHAR(50) '$.name',
    role NVARCHAR(50) '$.details.role',
    salary INT '$.details.salary'
);

Here, $.company.employees selects the array, while $.details.role and $.details.salary access properties inside each employee object.

Parse a JSON array stored in a table column

For a JSON array stored in a column, use CROSS APPLY OPENJSON to turn each array element into a row.

SELECT
    e.Id,
    j.ProductId,
    j.Quantity
FROM Orders AS e
CROSS APPLY OPENJSON(e.OrderItems)
WITH (
    ProductId INT '$.productId',
    Quantity INT '$.quantity'
) AS j;

This pattern is useful when one table row contains an array of products, transactions, permissions, tags, or other repeated JSON objects.

Validate JSON before parsing

Use ISJSON() when JSON may be malformed or when you want to filter valid JSON rows before parsing.

SELECT *
FROM EmployeeData
WHERE ISJSON(EmployeeJSON) = 1;

SQL Server JSON compatibility level

JSON functions were introduced in SQL Server 2016. OPENJSON requires database compatibility level 130 or higher. If SQL Server reports that OPENJSON is not recognized, check the database compatibility level.

SELECT name, compatibility_level
FROM sys.databases
WHERE name = DB_NAME();

For databases that support it, the compatibility level can be changed with:

ALTER DATABASE [DatabaseName]
SET COMPATIBILITY_LEVEL = 130;

SQL Server 2025 JSON support

SQL Server 2025 adds additional JSON capabilities, including the native json data type, JSON_CONTAINS, CREATE JSON INDEX, and expanded SQL/JSON path support. The native JSON data type is also generally available for Azure SQL Database and Azure SQL Managed Instance under supported SQL Server 2025 or Always-up-to-date configurations.

OPENJSON vs JSON_VALUE vs JSON_QUERY

Common SQL Server JSON parsing problems

• OPENJSON is not recognized: Check that the database compatibility level is 130 or higher.
• JSON_VALUE returns NULL: Check the JSON path and confirm that the requested property exists.
• Nested JSON is returned as text: Use JSON_QUERY for objects or arrays, or map the nested properties explicitly with OPENJSON ... WITH.
• Array values are difficult to query: Use OPENJSON with CROSS APPLY or OUTER APPLY.
• Invalid JSON causes parsing issues: Validate the input with ISJSON before processing it.

Frequently asked questions

Pro tip: For production workloads, validate incoming JSON, extract only the properties you need, and use OPENJSON to shred arrays instead of repeatedly parsing the same document in application code.

See more

Visual Studio Marketplace

SSIS Catalog Migration Wizard

Extend Visual Studio with an easy way to migrate SSIS Catalog projects.

Get it from Visual Studio Marketplace

Kunal Rathi

With over 15 years of experience in data engineering and analytics, I've assisted countless clients in gaining valuable insights from their data. As a dedicated supporter of Data, Cloud and DevOps, I'm excited to connect with individuals who share my passion for this field. If my work resonates with you, we can talk and collaborate.