Search code examples
sqljsonsql-servert-sqlopen-json

SQL Server parse nested json using OPENJSON


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;

Expected Output

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

Solution

  • 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