SQL Server parse nested json using OPENJSON-CodePudding

I am trying to read JSON that I would like to parse each collection in SQL Server.

The structure is as follows Root > Action > GoActionFiles / Pools

I would like to return a row for each GoActionFiles > GoActionFile, so it looks like the below;

The JSON is as follows;

DECLARE @Json NVARCHAR(MAX)
SET @Json = 
N'
{
   "Root":{
      "Action":{
         "ActionId":1,
         "OutcomeId":2,
         "ActionDateTime":"2022-11-22T14:28:20.9700312 00:00",
         "GoActionFiles":{
            "GoActionFile":[
               {
                  "Name":"Arigon",
                  "Status":"Failed"
               },
               {
                  "Name":"Butella",
                  "Status":"Passed"
               },
               {
                  "Name":"Chantice",
                  "Status":"Passed"
               },
               {
                  "Name":"Fordwat",
                  "Status":"Passed"
               }
            ]
         },
         "Pools":{
            "Pool":[
               {
                  "Name":"Arigon",
                  "Status":"Passed"
               },
               {
                  "Name":"Butella",
                  "Status":"Failed"
               },
               {
                  "Name":"Chantice",
                  "Status":"Failed"
               },
               {
                  "Name":"Fordwat",
                  "Status":"Failed"
               }
            ]
         },
         "ExtCheck":{
            "Score":800,
            "ExtStatus":"Passed",
            "IntScore":0
         }
   }
}
'

I have tried the following SQL so far;

    SELECT ActionId, a.GoActionFiles FROM OPENJSON(@Json, '$.Root.Action') WITH
(
    ActionId INT,
    GoActionFiles NVARCHAR(MAX) AS JSON
) AS a

CodePudding user response：

If only one actionID I suspect this would be more performant

Select ActionID           = JSON_VALUE(@Json,'$.Root.Action.ActionId')
      ,GoActionFileName   = [Name]
      ,GoActionFileStatus = [Status]
 From OpenJSON(@Json, '$.Root.Action.GoActionFiles.GoActionFile') 
      with ( [Name] varchar(150),
             [Status] varchar(150)
           ) AS a

If Multiple ID's

 SELECT A.ActionId
       ,GoActionFileName   = B.[Name]
       ,GoActionFileStatus = B.[Status]
   FROM OPENJSON(@Json, '$.Root.Action') WITH (
                                                ActionId INT,
                                                GoActionFiles NVARCHAR(MAX) AS JSON
                                              ) A
 Cross Apply ( Select * 
                From OpenJSON(GoActionFiles, '$.GoActionFile') 
                        with ( [Name]   varchar(150),
                               [Status] varchar(150)
                              ) B1 
             ) B

Both Return

ActionId    GoActionFileName    GoActionFileStatus
1           Arigon              Failed
1           Butella             Passed
1           Chantice            Passed
1           Fordwat             Passed