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

Answer 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 

Answer 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