I have a JSON structure where there are Sections, consisting of multiple Renders, which consist of multiple Fields.
How do I do 1 OPENJSON call on the lowest level (Fields) to get all information from there?
Here is an example JSON:
Declare @layout NVARCHAR(MAX) = N'
{
"Sections": [
{
"SectionName":"Section1",
"SectionOrder":1,
"Renders":[
{
"RenderName":"Render1",
"RenderOrder":1,
"Fields":[
{
"FieldName":"Field1",
"FieldData":"Data1"
},
{
"FieldName":"Field2",
"FieldData":"Data2"
}
]
},
{
"RenderName":"Render2",
"RenderOrder":2,
"Fields":[
{
"FieldName":"Field1",
"FieldData":"Data1"
},
{
"FieldName":"Field2",
"FieldData":"Data2"
}
]
}
]
},
{
"SectionName":"Section2",
"SectionOrder":2,
"Renders":[
{
"RenderName":"Render1",
"RenderOrder":1,
"Fields":[
{
"FieldName":"Field1",
"FieldData":"Data1"
}
]
},
{
"RenderName":"Render2",
"RenderOrder":2,
"Fields":[
{
"FieldName":"Field1",
"FieldData":"Data1"
},
{
"FieldName":"Field2",
"FieldData":"Data2"
}
]
}
]
}
]
}
'
Here is some example of code of a nested OPENJSON call, which works, but is very complex and can't be generated dynamically, how do I make it one level call?
SELECT SectionName, SectionOrder, RenderName, RenderOrder, FieldName, FieldData FROM (
SELECT SectionName, SectionOrder, RenderName, RenderOrder, Fields FROM (
select SectionName, SectionOrder, Renders
from OPENJSON(@layout,'$.Sections')
WITH (
SectionName nvarchar(MAX) '$.SectionName',
SectionOrder nvarchar(MAX) '$.SectionOrder',
Renders nvarchar(MAX) '$.Renders' as JSON
)
) as Sections
CROSS APPLY OPENJSON(Renders,'$')
WITH (
RenderName nvarchar(MAX) '$.RenderName',
RenderOrder nvarchar(MAX) '$.RenderOrder',
Fields nvarchar(MAX) '$.Fields' as JSON
)
) as Renders
CROSS APPLY OPENJSON(Fields,'$')
WITH (
FieldName nvarchar(MAX) '$.FieldName',
FieldData nvarchar(MAX) '$.FieldData'
)
This is what I would like to achieve:
select FieldName, FieldData
from OPENJSON(@layout,'$.Sections.Renders.Fields')
WITH (
FieldName nvarchar(MAX) '$.Sections.Renders.Fields.FieldName',
FieldData nvarchar(MAX) '$.Sections.Renders.Fields.FieldData'
)
While you can't get away with using only a single OPENJSON, you can simplify your query a bit to make it easier to create dynamically by removing the nested subqueries:
SELECT SectionName, SectionOrder, RenderName, RenderOrder, FieldName, FieldData
FROM OPENJSON(@layout, '$.Sections')
WITH (
SectionName NVARCHAR(MAX) '$.SectionName',
SectionOrder NVARCHAR(MAX) '$.SectionOrder',
Renders NVARCHAR(MAX) '$.Renders' AS JSON
)
CROSS APPLY OPENJSON(Renders,'$')
WITH (
RenderName NVARCHAR(MAX) '$.RenderName',
RenderOrder NVARCHAR(MAX) '$.RenderOrder',
Fields NVARCHAR(MAX) '$.Fields' AS JSON
)
CROSS APPLY OPENJSON(Fields,'$')
WITH (
FieldName NVARCHAR(MAX) '$.FieldName',
FieldData NVARCHAR(MAX) '$.FieldData'
)
EDIT:
If you have a primitive array, you can access the data using the value
property after you expose the nested array as a JSON field. Using the JSON from the comment below, you can do this to get the values from a primitive array:
DECLARE @layout NVARCHAR(MAX) = N'{ "id":123, "locales":["en", "no", "se"] }'
SELECT
a.id
, [Locale] = b.value
FROM OPENJSON(@layout, '$')
WITH (
id INT '$.id',
locales NVARCHAR(MAX) '$.locales' AS JSON
) a
CROSS APPLY OPENJSON(a.locales,'$') b