Microsoft SQL Server OPENJSON Working with nested JSON sub-arrays
Example
JSON may have complex structure with inner arrays. In this example, we have array of orders with nested sub array of OrderItems.
declare @json nvarchar(4000) = N'[
{"Number":"SO43659","Date":"2011-05-31T00:00:00",
"Items":[{"Price":11.99,"Quantity":1},{"Price":12.99,"Quantity":5}]},
{"Number":"SO43661","Date":"2011-06-01T00:00:00",
"Items":[{"Price":21.99,"Quantity":3},{"Price":22.99,"Quantity":2},{"Price":23.99,"Quantity":2}]}
]'
We can parse root level properties using OPENJSON that will return Items array AS JSON fragment. Then we can apply OPENJSON again on Items array and open inner JSON table. First level table and inner table will be "joined" like in the JOIN between standard tables:
SELECT *
FROM
OPENJSON (@json)
WITH ( Number varchar(200), Date datetime,
Items nvarchar(max) AS JSON )
CROSS APPLY
OPENJSON (Items)
WITH ( Price float, Quantity int)
Results:
| Number | Date | Items | Price | Quantity |
|---|---|---|---|---|
| SO43659 | 2011-05-31 00:00:00.000 | [{"Price":11.99,"Quantity":1},{"Price":12.99,"Quantity":5}] | 11.99 | 1 |
| SO43659 | 2011-05-31 00:00:00.000 | [{"Price":11.99,"Quantity":1},{"Price":12.99,"Quantity":5}] | 12.99 | 5 |
| SO43661 | 2011-06-01 00:00:00.000 | [{"Price":21.99,"Quantity":3},{"Price":22.99,"Quantity":2},{"Price":23.99,"Quantity":2}] | 21.99 | 3 |
| SO43661 | 2011-06-01 00:00:00.000 | [{"Price":21.99,"Quantity":3},{"Price":22.99,"Quantity":2},{"Price":23.99,"Quantity":2}] | 22.99 | 2 |
| SO43661 | 2011-06-01 00:00:00.000 | [{"Price":21.99,"Quantity":3},{"Price":22.99,"Quantity":2},{"Price":23.99,"Quantity":2}] | 23.99 | 2 |
