One order document can hide several orders, each with several line items. OPENJSON turns nested JSON into rows when you unwrap each array at its own level and preserve the parent identity.
Trace the Nested JSON Levels Before Querying
Start by drawing the document's structure. The outer object contains an orders array. Each order contains identifying properties and a lines array. Each line contains a product identifier and quantity. The query needs one expansion for the orders and another for their lines, with the parent fields carried into each output row.
I inspect a small accepted document before writing a WITH clause. Property names, numeric types, and array placement are part of the contract. A path guessed from one screenshot becomes fragile when another document arrives. Keep a representative payload available for controlled testing, with private values replaced by synthetic ones.
OPENJSON requires database compatibility level 130 or higher. Check that setting in the database running the example. The following payload is synthetic and self-contained. Keep it in the same query window when adapting the later expressions, or redeclare it for each independent test.
DECLARE @Document nvarchar(max) = N'{"orders":[
{"orderId":101,"customer":"North","lines":[
{"productId":1,"quantity":2},{"productId":2,"quantity":1}]},
{"orderId":102,"customer":"South","lines":[]},
{"orderId":103,"customer":"West"}]}';
SELECT [key], value, type
FROM OPENJSON(@Document, '$.orders');Expand an Order Through a Typed Schema
The WITH clause gives each accepted property a SQL column and type. Use explicit paths when the property names differ from column names. An inner object or array requires nvarchar(max) with AS JSON. That keeps the fragment available for the next expansion instead of asking for a scalar value.
DECLARE @Document nvarchar(max) = N'{"orders":[
{"orderId":101,"customer":"North","lines":[
{"productId":1,"quantity":2},{"productId":2,"quantity":1}]}]}';
SELECT OrderID, CustomerName, LineArray
FROM OPENJSON(@Document, '$.orders')
WITH (OrderID int '$.orderId',
CustomerName nvarchar(40) '$.customer',
LineArray nvarchar(max) '$.lines' AS JSON);Without AS JSON, the lines property is treated through scalar extraction behavior. An array is a different shape and does not provide the intended scalar. The resulting NULL can look like an empty order unless you check it. Shape errors deserve their own validation rather than a reassuring default.
Feed Each Inner Nested JSON Array to Another Expansion
CROSS APPLY runs the inner OPENJSON against each order's retained fragment. Carry the order identifier into the selected line rows. The query establishes the relationship between every line and its parent. It does not require joining arrays by their accidental displayed positions.
DECLARE @Document nvarchar(max) = N'{"orders":[
{"orderId":101,"customer":"North","lines":[
{"productId":1,"quantity":2},{"productId":2,"quantity":1}]},
{"orderId":102,"customer":"South","lines":[]}]}';
SELECT o.OrderID, o.CustomerName, l.ProductID, l.Quantity
FROM OPENJSON(@Document, '$.orders')
WITH (OrderID int '$.orderId',
CustomerName nvarchar(40) '$.customer',
LineArray nvarchar(max) '$.lines' AS JSON) AS o
CROSS APPLY OPENJSON(o.LineArray)
WITH (ProductID int '$.productId', Quantity int '$.quantity') AS l;An order with no inner elements contributes no rows through CROSS APPLY. Use OUTER APPLY when the report must preserve that order. Decide how to display its NULL line fields. A preserved parent and a real line with missing properties are distinct cases, even if some selected columns look alike.
Retain Array Positions From the Default Schema
OPENJSON without WITH returns key, value, and type. For an array, key contains the zero-based element index as text. Convert it to int when numeric ordering matters. Ordering its textual form puts a later multi-digit index ahead of a smaller one in a lexical sort.
DECLARE @Document nvarchar(max) = N'{"orders":[
{"orderId":101,"lines":[{"productId":1,"quantity":2},
{"productId":2,"quantity":1}]}]}';
SELECT CONVERT(int, a.[key]) AS OrderPosition,
o.OrderID, CONVERT(int, b.[key]) AS LinePosition,
l.ProductID, l.Quantity
FROM OPENJSON(@Document, '$.orders') AS a
CROSS APPLY OPENJSON(a.value)
WITH (OrderID int '$.orderId', LineArray nvarchar(max) '$.lines' AS JSON) AS o
CROSS APPLY OPENJSON(o.LineArray) AS b
CROSS APPLY OPENJSON(b.value)
WITH (ProductID int '$.productId', Quantity int '$.quantity') AS l
ORDER BY OrderPosition, LinePosition;WITH replaces the default schema, so its output does not automatically retain key. The extra default-schema layer preserves position before typed extraction. Keep those positions when the source sequence carries meaning. Array position is still different from a durable business identifier when a later payload reorders its lines.
Decide Whether Missing Properties Are Acceptable
Lax mode is the default. A missing path returns NULL or an empty rowset according to the extraction shape. Strict mode raises an error when the required path is absent. Neither mode decides whether a zero quantity, duplicate product, or missing business identity should be accepted.
DECLARE @Document nvarchar(max) = N'{"orderId":103}';
SELECT LineArray
FROM OPENJSON(@Document)
WITH (LineArray nvarchar(max) 'lax $.lines' AS JSON);
BEGIN TRY
SELECT LineArray
FROM OPENJSON(@Document)
WITH (LineArray nvarchar(max) 'strict $.lines' AS JSON);
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE() AS PathError;
END CATCH;Test missing, empty, and explicitly null properties separately. Confirm how your chosen path mode handles each accepted shape. A present empty array can be a valid order with no lines. A missing array can indicate an incomplete export. Treating both as an empty business record removes useful evidence.
Validate Types Without Silently Dropping Evidence
Typed extraction converts values to the requested SQL types. An invalid numeric value can raise a conversion error. If your import needs row-level exception handling, stage raw scalar text and apply TRY_CONVERT in a later validation query. Keep the raw document and source positions for explaining rejected values.
I keep rejection rules beside the expansion query. Check required identifiers, positive quantities, allowed property names, and duplicate line identities. Document which failures stop the whole payload and which produce reviewable exceptions. A parser that returns rows has completed only the structural part of the import.
JSON property matching uses case-sensitive semantics for paths. The names quantity and Quantity therefore deserve separate tests. Quoted path syntax also matters for names containing spaces or punctuation. Use the exact accepted property contract, rather than normalizing names by guesswork inside every report.
Protect Parent and Line Multiplicity
Expanding two unrelated arrays with separate CROSS APPLY operations can multiply every element from one by every element from the other. That is appropriate only when the business relationship is a Cartesian product. Do not interpret the resulting rows as aligned pairs merely because both arrays belong to one object.
Which identifier tells you that a line belongs to this order? Preserve it through staging and reconciliation. Compare expected parent populations, line populations, and aggregate quantities against the input contract. Validate joins to relational reference tables separately, since duplicate reference matches can multiply an already correct expansion.
An empty result is not automatically a successful import. Check whether the input had no orders, had missing paths, or failed eligibility rules later. Keep those outcomes distinct in the loading record. JSON offers flexible shapes, but the import still needs explicit success and failure meanings.
Keep the Nested JSON Query as a Document Contract
Nested JSON is manageable when each expansion has one clear level and purpose. Preserve parent identities, explicit SQL types, and source positions. Choose CROSS APPLY or OUTER APPLY from the result contract, and use lax or strict paths according to accepted missing-value behavior.
Rehearse nested JSON changes with old and new payload versions. Keep syntactic validation, typed conversion, and business checks visible as separate steps. Then the rows produced by OPENJSON have a meaning the rest of the database can safely rely on.
Related reading on this blog: Validating JSON Parameters Before a Procedure Uses Them and Storing JSON in SQL Server.
Expanding an array is not validating an order, it is turning a document structure into rows with traceable parentage.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
