json - 如何在数组数组上执行OPENJSON

Question

我有一个JSON结构，其中有Sections，由多个Renders组成，Renders由多个Field组成。

如何在最低级别(字段)上进行1 OPENJSON调用以从那里获取所有信息？

这是一个示例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"
                            }
                        ]
                    } 
                ]
            }
        ]
    }
'

这是嵌套OPENJSON调用的一些代码示例，该示例可以工作，但是非常复杂并且无法动态生成，如何使其成为一级调用？

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

这是我要实现的目标:

select FieldName, FieldData
from OPENJSON(@layout,'$.Sections.Renders.Fields') 
WITH (
    FieldName nvarchar(MAX) '$.Sections.Renders.Fields.FieldName',  
    FieldData nvarchar(MAX) '$.Sections.Renders.Fields.FieldData'
)

Answer 1

虽然不能仅使用单个OPENJSON，但可以通过删除嵌套的子查询来简化查询，使其更易于动态创建:

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

编辑:
如果您有原始数组，则在将嵌套数组公开为JSON字段之后，可以使用 value属性访问数据。使用下面注释中的JSON，您可以执行以下操作以从原始数组中获取值:

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