How to select a JSON array to a table

Question

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

If the orders array contains a single object: OPENJSON (@json, '$.orders[0].line_items') — lptr
– lptr, Commented Jan 12, 2023 at 15:56

John Cappelletti · Accepted Answer · 2023-01-12 16:00:42Z

1

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

answered Jan 12, 2023 at 16:00

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

DreamTeK Over a year ago

This is great and seems to be what I need, thank you. Is it possible to filter on the current order.id iteration? e.g where id = 123

Max Over a year ago

Just add ` where JSON_VALUE(A.Value,'$.id') = 123` at the end

DreamTeK Over a year ago

Ha. I have been looking at this json so long I forgot how to basic. You're a legend and a gent thank you sir.

Collectives™ on Stack Overflow

How to select a JSON array to a table

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related