Reading JSON in SQL Server With OPENJSON and a Schema

SQL Server stores JSON as text, and for most of its history OPENJSON has been the function that turns that text into rows. It is the equivalent of jsonb_array_elements in PostgreSQL, JSON_TABLE in MySQL and json_each in SQLite, and like them it exists in two forms: one that hands you the raw pieces and one that returns typed columns. This article explains both, how to unpack a nested document, how the join behaves with empty and missing arrays, and what to use instead when a single value is all you need.

The default form: key, value, type

OPENJSON(json_text) with no schema returns one row per property of an object or per element of an array, with three columns:

DECLARE @j nvarchar(max) = N'{"sku":"A1","qty":2,"gift":true,"note":null,"tags":["x","y"],"dims":{"w":1}}';

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

The result has six rows, one per property. key is the property name (or the zero-based position for an array), value is its value as nvarchar(max), and type is a number telling you what kind of JSON value it was:

The type column is what lets you distinguish a JSON null from a string and a number from a string that looks like a number, which JSON_VALUE cannot tell you because it returns text for everything. For "note":null the row has type 0 and a NULL value. For "gift":true the value is the text true and type is 3. Objects and arrays come back as their JSON text, ready to pass to another OPENJSON or to JSON_QUERY.

The default form is for exploring unknown documents, listing the keys, checking types and writing generic code. For a known shape, use the typed form.

The typed form: WITH

A WITH clause declares output columns, each with a SQL type and, optionally, a path:

SELECT *
FROM OPENJSON(@orderJson, '$.items')
WITH (
  sku   nvarchar(20)  '$.sku',
  qty   int           '$.qty',
  price decimal(10,2) '$.price',
  tags  nvarchar(max) '$.tags' AS JSON
);

The second argument to OPENJSON is the path of the array to unpack ($.items); leave it out for a document that is itself an array. Each column's path is relative to the element. If you omit the path, SQL Server looks for a property with the same name as the column, which is case-sensitive: a column named Sku does not match the key sku. That is a quiet source of NULL columns, so write the path every time.

The AS JSON modifier is how you keep a nested object or array: the column must be nvarchar(max), and the value comes back as JSON text. Without it, asking for an array into an nvarchar column in lax mode returns NULL and not the text, which surprises people who expected a string. Use AS JSON and unpack the column with another OPENJSON.

The conversion rules, per the documentation: a JSON number to int or decimal converts if it fits and fails or returns NULL if not (depending on mode), a JSON boolean to bit gives 1 or 0, a string to datetime2 parses the string if it is in a recognized format, and everything converts to nvarchar. OPENJSON rows come out in the order of the document.

Lax and strict

Paths begin with an optional lax or strict. The default is lax: a path that is missing, or a value that cannot be converted, gives NULL. strict raises an error instead, which is what you want when a missing field means the input is bad: 'strict $.sku' as a column path fails the statement if any element lacks sku. A mix is possible. Use strict for the required fields and lax for optional ones, in the same WITH clause.

Joining to the source row

The pattern for a table is CROSS APPLY:

SELECT o.id, i.sku, i.qty
FROM orders AS o
CROSS APPLY OPENJSON(o.doc, '$.items')
  WITH (sku nvarchar(20) '$.sku', qty int '$.qty') AS i;

CROSS APPLY evaluates the function for each row of orders, using that row's doc. As with an inner join, an order whose items array is empty, or missing, produces no rows and disappears from the result. OUTER APPLY keeps such an order with NULL in sku and qty. Use OUTER APPLY whenever the report must count every order.

Nested arrays chain the same way. If each item has a tags array, take the tags column AS JSON from the first call and unpack it with a second CROSS APPLY OPENJSON(i.tags); for an array of strings the default form returns the strings in value. There is no single clause that unpacks several levels at once, unlike Oracle's NESTED PATH.

Guard against bad text

A malformed document makes OPENJSON raise an error and abort the statement, with an "unexpected character at position" message. If a column might hold bad text, filter first with ISJSON:

SELECT o.id, i.sku
FROM orders AS o
CROSS APPLY OPENJSON(CASE WHEN ISJSON(o.doc) = 1 THEN o.doc ELSE N'[]' END, '$.items')
  WITH (sku nvarchar(20) '$.sku') AS i;

Do not rely on a WHERE ISJSON(o.doc) = 1 to protect the function: SQL Server may evaluate the APPLY before it applies the filter, so the CASE form is safer. A CHECK (ISJSON(doc) = 1) constraint on the column keeps bad text out in the first place.

One value or many

JSON_VALUE(doc, '$.customer.name') returns a single scalar as nvarchar(4000); it is the right tool for one or two fields. Each call parses the document again, so a query that calls it eight times for eight fields does eight parses. For many fields from one document, one OPENJSON ... WITH call returns them all at once. The other databases show the same effect: SQLite's repeated calls cost little because of a parse cache, and MySQL's JSON_TABLE reads the document once.

For a sub-document, JSON_QUERY(doc, '$.items') returns an object or array as JSON text, and JSON_VALUE on an object returns NULL in lax mode. Choose the function by the shape.

Indexing

OPENJSON output cannot be indexed, since it is computed at query time. To make a lookup by a field fast, add a computed column over JSON_VALUE and index that:

ALTER TABLE orders ADD customer_name AS JSON_VALUE(doc, '$.customer.name');
CREATE INDEX ix_orders_customer_name ON orders (customer_name);

A query that uses the same JSON_VALUE expression can then use the index, per the documentation. If the queries are mostly on a few fields, those fields are probably columns. The general argument is in the comparison of JSON in the four engines.

Building the reverse

FOR JSON produces JSON from rows, and it is covered in building JSON from query results with FOR JSON. Together, OPENJSON and FOR JSON let you shred and rebuild a document inside the database. A quick way to look at a document before writing the WITH clause is the JSON formatter in your browser.

When OPENJSON is the wrong function

You need a single value. JSON_VALUE and JSON_QUERY are shorter and read better in a WHERE clause.

The query runs constantly on a big table. Shredding at query time costs a parse for every row. Persist the rows into a table once.

You need to filter inside the array before unpacking. OPENJSON returns every element, and the filter happens afterward in WHERE. For existence checks, JSON_PATH_EXISTS (SQL Server 2022) or an EXISTS subquery on OPENJSON expresses the intent.

The data is on a pre-130 compatibility level. The function does not exist there; raise the level or use the older string functions.

You must know the exact error. The messages are short; add ISJSON checks and log the rows that fail.

The reference is Microsoft's documentation of OPENJSON and of JSON data in SQL Server. The engines I could run are covered in expanding JSONB into rows with jsonb_to_recordset and expanding JSON arrays with json_each.