How to select a JSON array to a table-CodePudding

Is it possible to output a nested array into a table?

I intend to loop over the orders array and capture the line_item id's for the current iteration.

SQL

DECLARE @json NVARCHAR(4000) = N'
{
  "orders":[
    {
      "id":123,
      "line_items":[
        {
          "id":1
        },
        {
          "id":2
        }
      ]
    }
  ]
};'

SELECT
  [LineId]
FROM OPENJSON (@json, '$.orders.line_items') WITH (
  [LineId] BIGINT '$.id'
);

Desired output

id
1
2

CodePudding user response：

Here is a working option using a CROSS APPLY to extract the array.

SELECT id = JSON_VALUE(B.Value,'$.id')
 FROM OPENJSON(@json, '$.orders') A
 CROSS APPLY OPENJSON (A.value, '$.line_items') as B

Results

id
1
2