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

Answer 1

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