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?

score 4 · Accepted Answer · answered Sep 09 '21 at 20:18

4

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

answered Sep 09 '21 at 20:18

squillman

13,363
3
41
60

Hmm, I am not familiar with OPENJSON. I will have to look into that more. They query you gave me errors out on my machine, but I can dig into OPENJSON and see if it can do what I need. (FYI, there are two errors it gives me: *Incorrect syntax near the keyword 'AS'.* on line 5 and *Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.* on lines 8 and 13) – Vaccano Sep 09 '21 at 20:26
Interesting. It works perfectly on my SQL 2019 instance with your sample JSON. Are you sure you have the whole query highlighted when running it? You also might need a semicolon somewhere above it if you are running it with other things. `WITH` is not part of a CTE in this case. – squillman Sep 09 '21 at 20:28
1

Ah, my DB's compatibility level is too lower for OPENJSON. I will see if I can get the Compatibility level raised. – Vaccano Sep 09 '21 at 20:29
1

Ah yes, that would do it. Needs to be 130 or higher. – squillman Sep 09 '21 at 20:29

JSON_QUERY to do a "Select Many"

1 Answers1