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 APPLYing -- that is, I don't know that you can in a single OPENJSON. Does this answer get you what you need? — ruffin
– ruffin, Commented Oct 16, 2017 at 23:43

zcoop98 · Accepted Answer · 2023-05-19 19:41:01Z

58

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, 2023 at 19:41

zcoop98

3,2852 gold badges26 silver badges38 bronze badges

answered Nov 22, 2017 at 20:13

digital.aaron

5,7152 gold badges27 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Ε Г И І И О Over a year ago

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 Over a year ago

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

Raymondo Over a year ago

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

ccdrm Over a year ago

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

ripvlan Over a year ago

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.

|

Bhadresh Patel · Accepted Answer · 2020-11-12 11:54:57Z

13

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

edited Nov 12, 2020 at 11:54

answered Nov 7, 2020 at 13:30

Bhadresh Patel

2,09019 silver badges20 bronze badges

1 Comment

ListenSoftware Louise Ai Agent Over a year ago

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

Wilham Ezequiel de Sousa · Accepted Answer · 2022-05-27 18:12:56Z

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 |

digital.aaron · Accepted Answer · 2019-12-03 23:51:47Z

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.

Collectives™ on Stack Overflow

How do you OPENJSON on Arrays of Arrays

4 Answers 4

6 Comments

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

1 Comment

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

6 Comments

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

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related