I have a JSON structure like this:
Declare @layout NVARCHAR(MAX) = N'
{
"Sections": [
{
"SectionName":"Section1",
"SectionOrder":1,
"Fields":[
{
"FieldName":"Field1",
"FieldData":"Data1"
},
{
"FieldName":"Field2",
"FieldData":"Data2"
}
]
},
{
"SectionName":"Section2",
"SectionOrder":2,
"Fields":[
{
"FieldName":"Field3",
"FieldData":"Data3"
},
{
"FieldName":"Field4",
"FieldData":"Data4"
}
]
}
]
}
'
How do I query to the equivalent of Sections.Fields.FieldName = 'Field3'?
select *
from OPENJSON(@layout,'$.Sections')
WITH (
SectionName nvarchar(MAX) '$.SectionName',
SectionOrder nvarchar(MAX) '$.SectionOrder',
Fields nvarchar(MAX) '$.Fields' as JSON
)
This is as far as I got, I can't go down any further or it will not return any results.
Figured it out after reading documentation, fully works!
SELECT SectionName, FieldName, FieldData FROM (
select *
from OPENJSON(@layout,'$.Sections')
WITH (
SectionName nvarchar(MAX) '$.SectionName',
SectionOrder nvarchar(MAX) '$.SectionOrder',
Fields nvarchar(MAX) '$.Fields' as JSON
)
) as Sections
CROSS APPLY OPENJSON(Fields,'$')
WITH (
FieldName nvarchar(MAX) '$.FieldName',
FieldData nvarchar(MAX) '$.FieldData'
)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With