JSON_QUERY to do a "Select Many"-CodePudding

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?

CodePudding user response：

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