Menu

SQL Server OPENJSON(): Parse JSON into Rows

OPENJSON(jsonExpression) is a table-valued function that turns the immediate properties of a JSON object or elements of an array into rows. Use it in the FROM clause to filter, join, or insert JSON data as relational rows. It was introduced in SQL Server 2016 (13.x). See Microsoft’s OPENJSON reference.

Compatibility and input types

For varchar or nvarchar JSON text, OPENJSON normally requires database compatibility level 130 or later. Microsoft documents a database-scoped configuration that can make built-in table-valued functions available at other compatibility levels. SQL Server 2025 (17.x) also accepts the native json data type as input. Check the target server, service, and database settings before using these features.

Return each property or array element

Without a WITH clause, OPENJSON returns a row for each property or array element, with key, value, and type columns. For an array, key contains the zero-based element index. The type values identify null, string, number, Boolean, array, and object values.

DECLARE @doc nvarchar(max) = N'{"orderId":1042,"status":"ready","items":[{"sku":"A-1","quantity":2}]}';

SELECT [key], value, type
FROM OPENJSON(@doc);

key     | value                                 | type
--------|---------------------------------------|-----
orderId | 1042                                  | 2
status  | ready                                 | 1
items   | [{"sku":"A-1","quantity":2}]      | 4

This default form returns only the first level. It does not recursively flatten nested objects or arrays.

Define columns with WITH

Use WITH to map JSON properties to typed SQL columns. Column names match JSON property names by default; add a JSON path when the names differ or when you need to select a nested property:

DECLARE @doc nvarchar(max) = N'{"orderId":1042,"items":[{"sku":"A-1","quantity":2},{"sku":"B-2","quantity":1}]}';

SELECT item.sku, item.quantity
FROM OPENJSON(@doc, '$.items')
WITH (
    sku nvarchar(32),
    quantity int
) AS item;

sku | quantity
----|---------
A-1 | 2
B-2 | 1

The optional second argument selects a nested object or array before parsing. Here, each item becomes a separate row and SQL Server converts quantity to int.

Keep nested objects or arrays with AS JSON

In an explicit WITH schema, scalar columns are extracted as SQL values. To return a nested object or array as JSON text, declare the column as nvarchar(max) AS JSON:

DECLARE @doc nvarchar(max) = N'{"orderId":1042,"customer":{"name":"Ada Lovelace"},"items":[{"sku":"A-1"}]}';

SELECT orderId, customer, items
FROM OPENJSON(@doc)
WITH (
    orderId int,
    customer nvarchar(max) AS JSON,
    items nvarchar(max) AS JSON
);

AS JSON is needed for nested objects and arrays; its output column must be nvarchar(max). You can pass those fragments to another OPENJSON call with CROSS APPLY when you need rows from a deeper level.

Return scalar text longer than 4,000 characters

Unlike JSON_VALUE(), which returns nvarchar(4000) by default, an explicit nvarchar(max) column lets OPENJSON return a longer scalar:

DECLARE @doc nvarchar(max) = N'{"description":"Long text goes here"}';

SELECT description
FROM OPENJSON(@doc)
WITH (description nvarchar(max) '$.description');

Missing paths and strict mode

Path matching is case-sensitive. The default mode is lax: if the optional rowset path is missing, OPENJSON returns an empty result set; if a column path is missing, it returns SQL NULL. Prefix a path with strict when a missing path should raise an error.

DECLARE @doc nvarchar(max) = N'{"items":[{"sku":"A-1"}]}';

SELECT [key], value
FROM OPENJSON(@doc, 'strict $.missing');

This statement raises an error because the required path does not exist.

For a scalar property, use JSON_VALUE(); for an object or array fragment, use JSON_QUERY(). For path syntax shared across functions, see the SQL Server JSON path guide. For paths and type codes in detail, see Microsoft’s OPENJSON documentation. Browse the SQL Server JSON function guide for the complete task-based list.

Advertisement