How do you OPENJSON on Arrays of Arrays

Question

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

Solution 1

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

Solution 2

This can be done by CROSS Applying the JSON child node with the parent node and using the JSON_Value() function, like shown below:

 DECLARE @json NVARCHAR(1000)   
    SELECT @json =    
    N'{   
      "OrderHeader": [  
        {   
          "OrderID": 100,  
          "CustomerID": 2000,   
          "OrderDetail": [   
            {  
              "ProductID": 2000,   
              "UnitPrice": 350      
            },      
            {             
              "ProductID": 3000,   
              "UnitPrice": 450  
            },   
            {               
              "ProductID": 4000,  
              "UnitPrice": 550   
            }   
          ]   
        }    
      ]   
    }'   
        
    SELECT   
        JSON_Value (c.value, '$.OrderID') as OrderID,    
        JSON_Value (c.value, '$.CustomerID') as CustomerID,    
        JSON_Value (p.value, '$.ProductID') as ProductID,    
        JSON_Value (p.value, '$.UnitPrice') as UnitPrice   
         
    FROM OPENJSON (@json, '$.OrderHeader') as c   
    CROSS APPLY OPENJSON (c.value, '$.OrderDetail') as p   
    
    Result 
    -------
    OrderID CustomerID  ProductID   UnitPrice
    100     2000        2000        350
    100     2000        3000        450
    100     2000        4000        550

Comments

[digital.aaron]

How is this different than the accepted answer? Your sample JSON looks malformed and your final SELECT produces two records for cake donuts with NULL for the Batterid' and Battertype`. I can see this introducing problems downstream of this query. If you have additional insight that would help the OP, please update your answer to further explain your answer.

[Ε Г И І И О]

How would you do the Cross Appy if the JSON array is primitive? Ex { "id":123, "locales":["en", "no", "se"] } What would be inside the With clause in that case?

[digital.aaron]

@ΕГИІИО I added your example to my answer.

[Raymondo]

Excellent answer. A very similar problem to which I have and your detailed response helped immensely

[Golden Lion]

thank you this save me a ton of time. I am importing numerous json structures into tables

[Ashraf Sada]

Your are a great help, thank so much dear

[ccdrm]

I agree with @Raymondo - excellent answer that immediately solved an issue with polygon coordinates stored in array form

[ripvlan]

Great examples. The default type of b.value though is nvarchar(max). If you want to change the type simply add a WITH statement to the second OPENJSON e.g. : CROSS APPLY OPENJSON(a.locales,'$') with ( [value] varchar(10) '$') b. Yes, I overrode [value], but you can name the column anything you like. The important part is the changing of the type.

[digital.aaron]

@ripvlan yup, hopefully that was apparent in the first example where I expanded out the nested JSON. I used NVARCHAR(MAX) for all the expanded fields, but those types could easily be changed for others, as the data requires.