Sql 如何通过CROSS APPLY获取多级嵌套JSON属性的值

发布于06月08日

我们有以下格式的JSON数据:

{
    "id": "f53283cc-5af7-4864-a3f6-86f69d81ae93",
    "SectionId": 5493,
    "Fields": [
        {
            "FieldId": 17943,
            "FieldValue": 
                "[
                    {
                        \"Web_Id\":\"1;#99999984_6_11_018111\",
                        \"EDF\":
                            [
                                {
                                    \"key\":\"Orders\",
                                    \"val\":\"1\"
                                }
                            ],
                        \"CCID\":\"1;#AVSPN004987\",
                        \"G\":null
                    }
                ]",
            "FieldName": "<p>Primary_Product</p>17943",
            "VersionID": 2
        }
    ]
}

从这些数据中，我可以使用下面的查询从FieldID、FieldValue、FieldName和VersionID等字段属性级别提取值.但是，我想知道是否可以从FieldValue元素中提取WebID或CCID？目前，我正在进行一次子筛选以获得值:

SELECT
  AC_ID
  , FieldID
  , CASE
    WHEN FieldName LIKE '%Item_Description%' THEN 'Item Description'
    WHEN FieldName LIKE '%Item_Number%' THEN 'Item Number'
    WHEN FieldName LIKE '%Primary_Product%' THEN 'Primary Product'
    ELSE ''
    END AS Field
  , CASE
    WHEN FieldID <> '17943' THEN FieldValue
    WHEN FieldID = '17943' THEN SUBSTRING(STUFF(FieldValue, 1, 15, ''), 1, CHARINDEX('"', STUFF(FieldValue, 1, 15, '')) - 1)
    ELSE NULL
    END AS FieldValue
  , SUBSTRING(STUFF(FieldValue, 1, 15, ''), 1, CHARINDEX('"', STUFF(FieldValue, 1, 15, '')) - 1)

FROM CTE_BASE
CROSS APPLY OPENJSON(CTE_BASE.Fields)
WITH (
  FieldId VARCHAR(10) '$.FieldId'
  , FieldName VARCHAR(MAX) '$.FieldName'
  , FieldValue VARCHAR(MAX) '$.FieldValue'
) AS CDB

DECLARE @json nvarchar(max) = N'{ "id":"f53283cc-5af7-4864-a3f6-86f69d81ae93", "SectionId":5493, "Fields":[ { "FieldId":17943, "FieldValue":"[{\"Web_Id\":\"1;#99999984_6_11_018111\",\"EDF\":[{\"key\":\"Orders\",\"val\":\"1\"}],\"CCID\":\"1;#AVSPN004987\",\"G\":null}]", "FieldName":"<p>Primary_Product</p>17943", "VersionID":2 } ] }'

SELECT * FROM OPENJSON(@json, '$.Fields') WITH ( FieldId VARCHAR(10) '$.FieldId', FieldName VARCHAR(MAX) '$.FieldName', FieldValue NVARCHAR(MAX) '$.FieldValue' ) j1 CROSS APPLY OPENJSON(j1.FieldValue) WITH ( Web_Id varchar(50) '$.Web_Id', CCID varchar(50) '$.CCID' ) j2

FieldId	FieldName	FieldValue	Web_Id	CCID
17943	<p>Primary_Product</p>17943	[{"Web_Id":"1;#99999984_6_11_018111","EDF":[{"key":"Orders","val":"1"}],"CCID":"1;#AVSPN004987","G":null}]	1;#99999984_6_11_018111	1;#AVSPN004987

FieldId

FieldName

FieldValue

Web_Id

CCID

17943

<p>Primary_Product</p>17943

[{"Web_Id":"1;#99999984_6_11_018111","EDF":[{"key":"Orders","val":"1"}],"CCID":"1;#AVSPN004987","G":null}]

1;#99999984_6_11_018111

1;#AVSPN004987

DECLARE @json1 nvarchar(max) = N'{ "Fields":[ {"FieldValue":"[{\"key\":\"value\"}]"} ] }' SELECT * FROM OPENJSON(@json1, '$.Fields') WITH ( FieldValue NVARCHAR(MAX) '$.FieldValue' ) j1 CROSS APPLY OPENJSON(j1.FieldValue) WITH ( [key] varchar(50) '$.key' ) j2

DECLARE @json2 nvarchar(max) = N'{ "Fields":[ {"FieldValue":[{"key":"value"}]} ] }' SELECT * FROM OPENJSON(@json2, '$.Fields') WITH ( FieldValue NVARCHAR(MAX) '$.FieldValue' AS JSON ) j1 CROSS APPLY OPENJSON(j1.FieldValue) WITH ( [key] varchar(50) '$.key' ) j2

FieldValue	key
[{"key":"value"}]	value

FieldValue

key

[{"key":"value"}]

value

Sql 如何通过CROSS APPLY获取多级嵌套JSON属性的值

推荐答案

Sql相关问答推荐

创建每小时重置的序列号

PG SQL中按条件聚合值

SQL Google Sheets：UNIQUE/DISTINCT和编码查询函数

返回包含列和包含其他列的列表的自定义查询

SQL基于多个值 Select 单行

SQL将 Select 查询作为新列添加到另一个 Select 查询

MariaDB查询在逗号分隔的字符串中查找多个值

如何嵌套两条SQL语句

雅典娜嵌套Json提取液

对列进行排序后，基于两列删除重复行

将日期时间转换为日期格式

如何从Spark SQL的JSON列中提取动态数量的键值对

排除具有部分匹配条件的记录

SQL查询正在工作，但返回空结果

为什么SQL in中的空子查询有时被视为null

Snowflake 中的分层数据

PostgreSQL分割字符串为子词并判断其是否存在于其他字符串中

正则表达式忽略特定数据部分的分隔符

如何通过CROSS APPLY获取多级嵌套JSON属性的值？

将有效数字作为 varchar 返回的 SQL 函数