使用STRING_SPLIT+JSON_VALUE无法解析CosmosDB JSON数据

问题核心原因 #

• JSON路径完全错误：你写的JSON_VALUE(CTE_BaseMetadata.FieldValue, '$[0].FieldValue')里，路径$[0].FieldValue根本不匹配数据结构——FieldValue存储的JSON数组元素是{"id":"xxx","text":"xxx"}，根本没有FieldValue字段。
• 误用STRING_SPLIT处理JSON：JSON数组不能靠字符串拆分来解析，STRING_SPLIT会破坏JSON的结构逻辑，导致无法正确识别有效数据。
• HTML实体未还原：你的CASE语句里出现了[{"id":"","text":""}]，说明实际数据里的双引号被转成了HTML实体"，不替换回双引号的话，SQL无法识别这是有效JSON。

修正后的查询语句 #

WITH CTE_Source AS (
    SELECT
        [TRFID]
        , [SectionId]
        , [rowtype]
        , [Fields]
    FROM OPENROWSET (
        -- 保留你的OPENROWSET原有配置
    ) AS CDB
),
CTE_BaseMetadata AS (
    SELECT
        TRFID
        , FieldId
        -- 先把HTML实体转成双引号，再处理空值
        , CASE
            WHEN REPLACE(FieldValue, '"', '"') = '[{"id":"","text":""}]' THEN NULL
            WHEN FieldValue IN ('null', '"null"', '', '[]') THEN NULL
            ELSE REPLACE(FieldValue, '"', '"')
          END AS FieldValue
    FROM CTE_Source
    CROSS APPLY OPENJSON(CTE_Source.Fields)
    WITH (
        FieldId VARCHAR(10) '$.FieldId'
        , FieldValue NVARCHAR(MAX) '$.FieldValue'
    ) AS CDB
),
CTE_MultiJSONMetadata AS (
    SELECT
        b.TRFID
        , b.FieldId
        , b.FieldValue
        -- 直接从JSON数组里提取id和text字段
        , j.id
        , j.text
    FROM CTE_BaseMetadata b
    -- 用OPENJSON解析FieldValue中的JSON数组，替代STRING_SPLIT
    CROSS APPLY OPENJSON(b.FieldValue)
    WITH (
        id VARCHAR(50) '$.id'
        , text VARCHAR(50) '$.text'
    ) AS j
    WHERE
        b.FieldId IN ('32846', '32847')
        AND b.FieldValue IS NOT NULL
)
SELECT * FROM CTE_MultiJSONMetadata

关键修正说明 #

1. 还原JSON格式：通过REPLACE(FieldValue, '"', '"')把HTML实体转成双引号，让SQL能识别FieldValue为有效JSON字符串。
2. 用OPENJSON替代STRING_SPLIT：直接解析JSON数组结构，精准提取每个元素的id和text，避免字符串拆分带来的错误。
3. 修正空值判断逻辑：合并重复的空值判断，简化代码同时覆盖所有无效场景。
4. 移除错误路径：删掉完全不匹配的$[0].FieldValue路径，改用OPENJSON的字段映射直接获取数据。

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