时间戳

首页 > 解决方案 > 如何使用具有未确定/动态路径的嵌套 JSON 的 OPENJSON 方法？

sql - 如何使用具有未确定/动态路径的嵌套 JSON 的 OPENJSON 方法？

问题描述

我正在使用以动态 json 形式输出数据的 API，即 json 对象的某些元素的位置不固定，这导致 json 数组中的某些元素的路径是动态的。例如在 JSON 对象下面的“Property”数组下可以属于 n 级嵌套。OPENJSON 是否有任何搜索/查找功能，可以找到名称为“Property”的数组的位置并且只需将元素打印到表中？

这是工作模型，但我必须对数组使用强制索引 ('$.rows[2]') 以显示所需的结果。但是，Property 数组可以在任何地方。

JSON

Declare @Jsonobj as nvarchar(max)
Select @Jsonobj = N'{
    "ID": "StudentInformation",
    "Name": "Student Information",
    "Type": "s_info",
    "Details": [
        "Student Information",
        "Greendale Community College"
    ],
    "Date": "21 October 2021",
    "Rows": [
        {
            "RowType": "Header",
            "Cells": [
                {
                    "Value": ""
                },
                {
                    "Value": "21 Feb 2021"
                },
                {
                    "Value": "22 Aug 2020"
                }
            ]
        },
        {
            "RowType": "Section",
            "Title": "Class",
            "Rows": []
        },
        {
            "RowType": "Section",
            "Title": "Grade",
            "Rows": [
                {
                    "RowType": "Row",
                    "Cells": [
                        {
                            "Value": "5A",
                            "Property": [
                                {
                                    "Id": "1",
                                    "Value": "John Smith"
                                }
                            ]
                        },
                        {
                            "Value": "5A",
                            "Property": [
                                {
                                    "Id": "2",
                                    "Value": "Jane Doe"
                                }
                            ]
                        },
                        {
                            "Value": "5B",
                            "Property": [
                                {
                                    "Id": "1",
                                    "Value": "Ben Frank"
                                }
                            ]
                        }
                    ]
                }
            ]
        }
    ]
}'

SQL

SELECT JSON_VALUE(v.value, 'strict $.Value') as Names
FROM OPENJSON(@Jsonobj, 'strict $.Rows[2].Rows') c
CROSS APPLY OPENJSON(c.value, 'strict $.Cells') p
CROSS APPLY OPENJSON(p.value, 'strict $.Property') v

标签： sqljsonopen-jsonjson-value

解决方案

这是我发现的有效方法：

SELECT d.value Names
 FROM OPENJSON(@Jsonobj,'$.Rows')
 WITH([Rows] NVARCHAR(MAX) AS JSON) A
 OUTER APPLY OPENJSON(A.[Rows]) B
 OUTER APPLY OPENJSON(B.[value],'$.Cells') C
 OUTER APPLY OPENJSON(c.[value],'$.Property') WITH (Value NVARCHAR(MAX)) d
 where d.value is not null

推荐阅读