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

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