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

I think you have to keep `CROSS APPLY`ing -- that is, I don't know that you can in a single `OPENJSON`. Does [this answer get you what you need](https://stackoverflow.com/a/37218450/1028230)? — ruffin, Oct 16 '17 at 23:43

score 53 · Accepted Answer · edited May 19 '23 at 19:41

53

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

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

edited May 19 '23 at 19:41

zcoop98

2,590
1
18
31

answered Nov 22 '17 at 20:13

digital.aaron

5,435
2
24
43

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? – Ε Г И І И О Apr 28 '20 at 11:37
1

@ΕГИІИО I added your example to my answer. – digital.aaron Apr 28 '20 at 15:33
1

Excellent answer. A very similar problem to which I have and your detailed response helped immensely – Raymondo May 06 '20 at 16:02
1

I agree with @Raymondo - excellent answer that immediately solved an issue with polygon coordinates stored in array form – ccdrm Oct 04 '21 at 16:11
2

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. – ripvlan May 17 '22 at 19:05
@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. – digital.aaron May 18 '22 at 01:47

Bhadresh Patel · Answer 2 · 2020-11-12T11:54:57.933

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

thank you this save me a ton of time. I am importing numerous json structures into tables — Golden Lion, Nov 11 '20 at 22:28

Wilham Ezequiel de Sousa · Answer 3 · 2022-05-27T18:12:56.697

select 
  json_query(questionsList.value, '$.answers'), 
  json_value(answersList2.value, '$.text[0].text') as text,
  json_value(answersList2.value, '$.value') as code
from RiskAnalysisConfig c
  outer apply OpenJson(c.Configuration, '$.questions') as questionsList
  outer apply OpenJson(questionsList.value, '$.answers') as answersList2
where questionsList.value like '%"CATEGORIES"%'

Sample of List of answers for each question into list

questionsList list level 1
answersList2 list level 2

Json sample

    {
        "code": "Code x",
        "questions": [
            {},
            {},
            {
                "code": "CATEGORIES",
                "text": [
                    {
                        "text": "How old years are you?",
                        "available": true
                    }
                ],
                "answers": [
                    {
                        "text": [
                            {
                                "text": "more than 18",
                                "available": true
                            }
                        ],
                        "text": [
                            {
                                "text": "less than 18",
                                "available": true
                            }
                        ]
                    }
                ]
            }
        ]
    }



Partial result
| text                   | code         |
| ---                    | ---          |
| How old years are you? | more than 18 |
| How old years are you? | lass than 18 |

score -1 · Answer 4 · edited Dec 03 '19 at 23:51

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.Battertype
FROM MstJson d
CROSS 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 a
CROSS APPLY
    OPENJSON(Batters, '$')
    WITH
    (
        Batter NVARCHAR(MAX) '$.batter' AS JSON
    ) AS b
CROSS APPLY
    OPENJSON(Batter, '$')
    WITH
    (
        Batterid INT '$.id'
        ,Battertype NVARCHAR(MAX) '$.type'
    ) AS c
WHERE d.ID = 12; ---above Json Code is on Id 12 of Table MstJson

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. — digital.aaron, Dec 04 '19 at 00:02

How do you OPENJSON on Arrays of Arrays

4 Answers4

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