Reading json array into rows in SQL Server

Question

Given the sample json data below, how can I write a query to pull the array data all in one step? My goal is to have one row for each item in the ActionRecs array (4). My actual json is more complicated but I think this gives a good example of my goal.

declare @json2 nvarchar(max)
set @json2 = '{
    "RequestId": "1",
    "ActionRecs": [
        {
            "Type": "Submit",
            "Employee": "Joe"
        },
        {
            "Type": "Review",
            "Employee": "Betty"
        },
        {
            "Type": "Approve",
            "Employee": "Sam"
        },
        {
            "Type": "Approve",
            "Employee": "Bill"
        }
    ]
}'

SELECT x.*
, JSON_QUERY(@json2, '$.ActionRecs') as ActionArray
from OPENJSON(@json2) 
with (Id varchar(5) '$.RequestId') as x

Zhorov · Accepted Answer · 2023-03-23 12:58:54Z

You may use one of the following approaches:

OPENJSON() with explicit schema and an additional CROSS APPLY operator
a combination of JSON_VALUE() and OPENJSON() using the appropriate paths:

T-SQL:

-- JSON
DECLARE @json nvarchar(max)
SET @json = N'{
    "RequestId": "1",
    "ActionRecs": [
        {"Type": "Submit", "Employee": "Joe"},
        {"Type": "Review", "Employee": "Betty"},
        {"Type": "Approve", "Employee": "Sam"},
        {"Type": "Approve", "Employee": "Bill"}
    ]
}'

-- Two OPENJSON() calls
SELECT i.Id, a.[Type], a.[Employee]
FROM OPENJSON(@json) WITH (
   Id varchar(5) '$.RequestId',
   ActionRecs nvarchar(max) '$.ActionRecs' AS JSON
) i
CROSS APPLY OPENJSON(i.ActionRecs) WITH (
   [Type] nvarchar(max) '$.Type',
   [Employee] nvarchar(max) '$.Employee'
) a

-- A combination of JSON_VALUE() and OPENJSON():
SELECT JSON_VALUE(@json, '$.RequestId') AS Id, [Type], [Employee]
FROM OPENJSON(@json, '$.ActionRecs') WITH (
   [Type] nvarchar(max) '$.Type',
   [Employee] nvarchar(max) '$.Employee'
)

Result:

Id  Type    Employee
1   Submit  Joe
1   Review  Betty
1   Approve Sam
1   Approve Bill

Collectives™ on Stack Overflow

Reading json array into rows in SQL Server

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related