How do you OPENJSON on Arrays of Arrays

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, FieldDataFROM 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) aCROSS APPLY OPENJSON(a.locales,'$') b

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

I have the JSON code and inserted into the table called MstJson, the column name which contains JSON code is JSON data.JSON Code :

[    {       "id":100,      "type":"donut",      "name":"Cake",      "ppu":0.55,      "batters":{          "batter":[             {                "id":"1001",               "type":"Regular"            },            {                "id":"1002",               "type":"Chocolate"            },            {                "id":"1003",               "type":"Blueberry"            },            {                "id":"1004",               "type":"Havmor",               "BusinessName":"HussainM"            },            "id",            "type"         ]      },      "topping":[          {             "id":"5001",            "type":"None"         },         {             "id":"5002",            "type":"Glazed"         },         {             "id":"5005",            "type":"Sugar"         },         {             "id":"5007",            "type":"Powdered Sugar"         },         {             "id":"5006",            "type":"Chocolate with Sprinkles"         },         {             "id":"5003",            "type":"Chocolate"         },         {             "id":"5004",            "type":"Maple"         }      ]   },   {       "id":"0002",      "type":"donut",      "name":"Raised",      "ppu":0.55,      "batters":{          "batter":[             {                "id":"1001",               "type":"Regular"            }         ]      },      "topping":[          {             "id":"5001",            "type":"None"         },         {             "id":"5002",            "type":"Glazed"         },         {             "id":"5005",            "type":"Sugar"         },         {             "id":"5003",            "type":"Chocolate"         },         {             "id":"5004",            "type":"Maple"         }      ]   },   {       "id":"0003",      "type":"donut",      "name":"Old Fashioned",      "ppu":0.55,      "batters":{          "batter":[             {                "id":"1001",               "type":"Regular"            },            {                "id":"1002",               "type":"Chocolate"            }         ]      },      "topping":[          {             "id":"5001",            "type":"None"         },         {             "id":"5002",            "type":"Glazed"         },         {             "id":"5003",            "type":"Chocolate"         },         {             "id":"5004",            "type":"Maple"         }      ]   }]

Sql Code for OpenJson using CrossApply (Nested Array):

SELECT    d.ID    ,a.ID    ,a.Type    ,a.Name    ,a.PPU    ,c.Batterid    ,c.BattertypeFROM MstJson dCROSS APPLY    OPENJSON(Jsondata)    WITH    (        ID NVARCHAR(MAX) '$.id'        ,Type NVARCHAR(MAX) '$.type'        ,Name NVARCHAR(MAX) '$.name'        ,PPU DECIMAL(18, 2) '$.ppu'        ,Batters NVARCHAR(MAX) '$.batters' AS JSON    ) AS aCROSS APPLY    OPENJSON(Batters, '$')    WITH    (        Batter NVARCHAR(MAX) '$.batter' AS JSON    ) AS bCROSS APPLY    OPENJSON(Batter, '$')    WITH    (        Batterid INT '$.id'        ,Battertype NVARCHAR(MAX) '$.type'    ) AS cWHERE d.ID = 12; ---above Json Code is on Id 12 of Table MstJson