如何在Arrays of Arrays上打开OpenJSON

mfpqipee 于 2023-05-23 发布在其他

关注(0)|答案(4)|浏览(178)

我有一个JSON结构，其中有章节，由多个渲染器组成，由多个字段组成。
如何在最低层（字段）上调用1 OPENJSON以获取所有信息？
下面是一个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"
                            }
                        ]
                    } 
                ]
            }
        ]
    }
'

下面是一些嵌套OPENJSON调用的代码示例，它可以工作，但非常复杂，不能动态生成，我如何使它成为一个级别的调用？

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

这就是我想要实现的：

select FieldName, FieldData
from OPENJSON(@layout,'$.Sections.Renders.Fields') 
WITH (
    FieldName nvarchar(MAX) '$.Sections.Renders.Fields.FieldName',  
    FieldData nvarchar(MAX) '$.Sections.Renders.Fields.FieldData'
)

JSON

来源：https://stackoverflow.com/questions/40476356/how-do-you-openjson-on-arrays-of-arrays

4条答案

按热度按时间

hec6srdp1#

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"%'

每个问题的答案列表样本

questionsList列表级别1
answersList2列表级别2

JSON样本

{
        "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 |

赞(0）回复(0）举报 2023-05-23

kpbpu0082#

我有JSON代码并插入到名为MstJson的表中，包含JSON代码的列名就是JSON数据。JSON代码：

[ 
   { 
      "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"
         }
      ]
   }
]

使用CrossApply（嵌套数组）的OpenJson SQL代码：

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

赞(0）回复(0）举报 2023-05-23

o0lyfsai3#

虽然你不能只使用一个OPENJSON，但你可以通过删除嵌套的子查询来简化查询，使其更容易动态创建：

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

如果您有一个基元数组，则可以在将嵌套数组公开为JSON字段后使用value属性访问数据。使用下面注解中的JSON，您可以从原始数组中获取值：

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

赞(0）回复(0）举报 2023-05-23

osh3o9ms4#

这可以通过CROSS将JSON子节点与父节点一起应用，并使用JSON_Value（）函数来完成，如下所示：

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

赞(0）回复(0）举报 2023-05-23

我来回答

如何在Arrays of Arrays上打开OpenJSON

4条答案

这可以通过CROSS将JSON子节点与父节点一起应用，并使用JSON_Value（）函数来完成，如下所示：

相关问题

热门标签

最新问答