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

One possible approach is to use OPENJSON() with explicit schema and an additional CROSS APPLY operator:

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"}
    ]
}'

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

Output:

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