CODEWITHSUNDEEP
sqlarraysjsonsql-serveropen-json
ptownbro
ptownbro

Reputation: 1288

How to Set OPENJSON Path to Nested Array

I'm trying to set the path for my OPENJSON function for the nested array, but it's not working. Tried different variations and examples/resources I found online and still cannot figure it out.

Any ideas?

EDIT:

To be clear, I know how to do this with CROSSAPPLY and other methods. My question is in regards on how to do this specifically with the OPENJSON function's path parameter if possible.

Here's my code:

DECLARE @json NVARCHAR(MAX);
SET @json = '
{
    "orders": [
        {
            "id":"1",
            "date":"7/4/2020",
            "orderlines": [
                {"id": "1", "amount": 100}, 
                {"id": "2", "amount": 200}
            ]
        },
        {
            "id":"2",
            "date":"7/4/2020",
            "orderlines": [
                {"id": "3", "amount": 300}, 
                {"id": "4", "amount": 400}
            ]
        }
    ]
}
'
-- None of these return results. How do I specify the path to the "orderlines" array?
SELECT * FROM OPENJSON(@json,'$.orderlines');
SELECT * FROM OPENJSON(@json,'$.orderlines[1]');
SELECT * FROM OPENJSON(@json,'$.orders.orderlines');
SELECT * FROM OPENJSON(@json,'$.orders.orderlines[1]');

-- This works:
SELECT * FROM OPENJSON(@json,'$.orders');

Upvotes: 5

Views: 4808

Answers (3)

ptownbro
ptownbro

Reputation: 1288

To make it clear for others who may be viewing this, the part of Iman Kazemi's response that was the answer was the following of what he wrote:

SELECT * FROM OPENJSON(@json, '$.orders[0].orderlines[0]')

I neglected to specify the index on the order's array.

Thanks again to Iman.

Upvotes: 1

Iman Kazemi
Iman Kazemi

Reputation: 552

You can use WITH and put a name on inside values and use CROSS APPLY to use them in another OPENJSON. Now you can have all inside objects together.

SELECT orderlines.id, orderlines.amount 
FROM OPENJSON(@json, '$.orders') WITH (orderlines NVARCHAR(MAX) '$.orderlines' AS JSON) orders
CROSS APPLY OPENJSON(orders.orderlines) WITH (id INT '$.id', amount INT '$.amount') orderlines

1

Learn more here.

Also if need to get specific item in array:

SELECT * FROM OPENJSON(@json, '$.orders[0].orderlines[0]')
-- OR
SELECT JSON_VALUE(@json, '$.orders[0].orderlines[0].amount')

Upvotes: 5

sacse
sacse

Reputation: 3744

You can try the following:

SELECT *
FROM OPENJSON (@json, '$.orders')
WITH (
    id INT '$.id',
    [date] VARCHAR(10) '$.date',
 
    orderlines_id1 INT '$.orderlines[0].id',
    orderlines_amount1 MONEY '$.orderlines[0].amount', 
 
    orderlines_id2 INT '$.orderlines[1].id',
    orderlines_amount2 MONEY '$.orderlines[1].amount'
) AS Orders

Please see the db<>fiddle here.

Upvotes: 0

Related Questions