How to Set OPENJSON Path to Nested Array

Question

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');

Colliery

Answer 1

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

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')

Bate

As I mentioned in my OP, I already know how to do this with CROSSAPPLY. However, you suggestion at the bottom was the answer. Thanks! – Colliery

Answer 2

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.

Colliery

Answer 3

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.

Resurge

I appreciate your response, however, I'm looking for how I can do this with the OPENJSON function's path argument . I know how to do this other ways. I've edited my OP to make it more clear. thanks – Colliery