我有一个像这样的JSON结构:
我该如何查询等效于Sections.Fields.FieldName = 'Field3'的内容?
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"
}
]
}
]
}
'
我该如何查询等效于Sections.Fields.FieldName = 'Field3'的内容?
select *
from OPENJSON(@layout,'$.Sections')
WITH (
SectionName nvarchar(MAX) '$.SectionName',
SectionOrder nvarchar(MAX) '$.SectionOrder',
Fields nvarchar(MAX) '$.Fields' as JSON
)
这是我所能到达的最远位置,如果再往下翻将不会返回任何结果。
CROSS APPLY ( SELECT * FROM OPENJSON(Fields) WITH (FieldName nvarchar(max))) AS Fields
,它允许您为其提供一个表名。 - John Henckel