How do you OPENJSON on Arrays of Arrays
<p>I have a JSON structure where there are Sections, consisting of multiple Renders, which consist of multiple Fields.</p>

<p>How do I do 1 OPENJSON call on the lowest level (Fields) to get all information from there?</p>

<p>Here is an example JSON:</p>

<pre><code>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"
                            }
                        ]
                    } 
                ]
            }
        ]
    }
'
</code></pre>

<p>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?</p>

<pre><code>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'
)
</code></pre>

<p>This is what I would like to achieve:</p>

<pre><code>select FieldName, FieldData
from OPENJSON(@layout,'$.Sections.Renders.Fields') 
WITH (
    FieldName nvarchar(MAX) '$.Sections.Renders.Fields.FieldName',  
    FieldData nvarchar(MAX) '$.Sections.Renders.Fields.FieldData'
)
</code></pre>