Hi all,
I am trying to read the following JSON code in SQL Server:
declare @json nvarchar(max)
set @json =
'
{
"WorkId":121,
"Code":"UK_AAT",
"UpdateAttributes":
{
"TriggerDateMapping":
{
"3N":"N/A",
"10N": "N/A"
},
"OverrideRules":
[
{"Busket Rule":"A1",
"Exit_Window": 15,
"Strategy": "STE Triggered",
"Start_Date": "Month 2"
},
{"Busket Rule":"C2",
"Exit_Window": 44,
"Strategy": "STE",
"Start_Date": "Month 2"
},
{"Busket Rule":"D1",
"Exit_Window": 128,
"Strategy": "Ignore",
"Start_Date": "Q2"
}
]
}
}
'
--select isjson(@json)
select *
from openjson(@json)
with (
WorkId int '$.WorkId',
[Busket Rule] varchar(200) '$.UpdateAttributes.OverrideRules[0]."Busket Rule"',
Exit_Window int '$.UpdateAttributes.OverrideRules[0].Exit_Window',
Startegy varchar(200) '$.UpdateAttributes.OverrideRules[0].Strategy',
Start_Date varchar(100) '$.UpdateAttributes.OverrideRules[0].Start_Date'
)
it reads correctly, but only for the 1st entry of array [index 0]. To reading all the rest, I have to manually change it to 1,2, and so on. How I can read it so it will look like table?
Thanks
You need multiple OPENJSON functions.
select j.WorkId, r.[Busket Rule], r.Exit_Window, r.Startegy, r.Start_Date
from openjson(@json)
with (
WorkId int '$.WorkId',
OverrideRules NVARCHAR(MAX) '$.UpdateAttributes.OverrideRules' AS JSON
) j
CROSS APPLY OPENJSON(j.OverrideRules)
WITH (
[Busket Rule] varchar(200) '$."Busket Rule"',
Exit_Window int '$.Exit_Window',
Startegy varchar(200) '$.Strategy',
Start_Date varchar(100) '$.Start_Date'
) AS r
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
April 2, 2024 at 6:58 pm
Thanks a lot, Drew.
It works as expected.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply