JSON_QUERY to do a "Select Many"

Question

I have a JSON variable that looks like this (the real one is more complex):

DECLARE @myJson VARCHAR(3000) = '{
    "CustomerId": "123456",
    "Orders": [{
            "OrderId": "852",
            "OrderManifests": [{
                    "ShippedProductId": 884,
                    "ProductId": 884
                }, {
                    "ShippedProductId": 951,
                    "ProductId": 2564
                }    
            ]
        }, {
            "OrderId": "5681",
            "OrderManifests": [{
                    "ShippedProductId": 198,
                    "ProductId": 4681
                }, {
                    "ShippedProductId": 8188,
                    "ProductId": 8188
                }, {
                    "ShippedProductId": 144,
                    "ProductId": 8487
                }
            ]
        }
    ]
}'

In the end, I need to know if any of the ShippedProductId values match their corresponding ProductId (in the same JSON object).

I started in by trying to get a list of all the OrderManifests. But while this will get me the array of orders:

SELECT JSON_QUERY(@myJson, '$.Orders')

I can't seem to find a way to get a list of all the OrderManifests across all the entries in the Orders array. This does not work:

SELECT JSON_QUERY(@myJson, '$.Orders.OrderManifests')

Is there a way to do a Select Many kind of query to get all the OrderManifests in the Orders array?

Answer 1

Use OPENJSON and CROSS APPLY to drill down into your objects.

This should do it for you:

SELECT j.CustomerId,o.OrderId, m.ShippedProductId, m.ProductId
FROM OPENJSON(@myJson)
WITH (
    CustomerId NVARCHAR(1000),
    Orders NVARCHAR(MAX) AS JSON
) j
CROSS APPLY OPENJSON(j.Orders)
WITH (
    OrderId NVARCHAR(1000),
    OrderManifests NVARCHAR(MAX) AS JSON
) o
CROSS APPLY OPENJSON(o.OrderManifests)
WITH (
    ShippedProductId INT,
    ProductId int
) m
WHERE m.ShippedProductId = m.ProductId;

This query returns:

CustomerId  |  OrderId  |  ShipedProductId  |  ProductId
------------+-----------+-------------------+-------------
123456      |    852    |       884         |     884
------------+-----------+-------------------+-------------
123456      |   5681    |      8188         |    8188