Microsoft SQL Server 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:

NumberDateItemsPriceQuantity
SO436592011-05-31 00:00:00.000[{"Price":11.99,"Quantity":1},{"Price":12.99,"Quantity":5}]11.991
SO436592011-05-31 00:00:00.000[{"Price":11.99,"Quantity":1},{"Price":12.99,"Quantity":5}]12.995
SO436612011-06-01 00:00:00.000[{"Price":21.99,"Quantity":3},{"Price":22.99,"Quantity":2},{"Price":23.99,"Quantity":2}]21.993
SO436612011-06-01 00:00:00.000[{"Price":21.99,"Quantity":3},{"Price":22.99,"Quantity":2},{"Price":23.99,"Quantity":2}]22.992
SO436612011-06-01 00:00:00.000[{"Price":21.99,"Quantity":3},{"Price":22.99,"Quantity":2},{"Price":23.99,"Quantity":2}]23.992