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
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