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

原始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
        }
    ]
}

当前实现与需求 #

目前已通过查询提取Fields层级的FieldID、FieldValue、FieldName等值，但FieldValue本身是嵌套的JSON数组字符串，当前用SUBSTRING截取Web_Id的值，想知道有没有更可靠的方式直接提取Web_Id或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

更可靠的解决方案：嵌套使用OPENJSON #

因为FieldValue是JSON数组格式的字符串，可以再次用OPENJSON解析，直接提取目标字段，避免字符串截取的脆弱性（比如JSON格式微调就会导致截取失败）。

优化后的查询：

SELECT
  AC_ID
  , CDB.FieldID
  , CASE
    WHEN CDB.FieldName LIKE '%Item_Description%' THEN 'Item Description'
    WHEN CDB.FieldName LIKE '%Item_Number%' THEN 'Item Number'
    WHEN CDB.FieldName LIKE '%Primary_Product%' THEN 'Primary Product'
    ELSE ''
    END AS Field
  , CASE
    WHEN CDB.FieldID <> '17943' THEN CDB.FieldValue
    ELSE JSON_VALUE(FP.value, '$.Web_Id')
    END AS FieldValue
  -- 直接提取Web_Id和CCID
  , JSON_VALUE(FP.value, '$.Web_Id') AS Web_Id
  , JSON_VALUE(FP.value, '$.CCID') AS CCID
FROM CTE_BASE
CROSS APPLY OPENJSON(CTE_BASE.Fields)
WITH (
  FieldId VARCHAR(10) '$.FieldId'
  , FieldName VARCHAR(MAX) '$.FieldName'
  , FieldValue VARCHAR(MAX) '$.FieldValue'
) AS CDB
-- 针对FieldID=17943的情况，解析FieldValue中的JSON数组
OUTER APPLY OPENJSON(CDB.FieldValue) AS FP
-- 如需保留其他FieldID的记录，可调整条件：
-- WHERE CDB.FieldID = '17943' OR CDB.FieldID <> '17943'

说明 #

• 用OPENJSON(CDB.FieldValue)解析FieldValue的JSON数组，FP.value对应数组中的每个对象
• JSON_VALUE(FP.value, '$.Web_Id')直接提取对象中的Web_Id值，同理$.CCID提取CCID
• 这种方法不依赖字符串的固定位置，只要JSON结构不变，就能稳定提取数据，比字符串截取更健壮

内容的提问来源于stack exchange，提问作者Milo Ulver