SQL Server兼容级别100下，不使用OPENJSON提取JSON数组值的方法

在兼容级别100的SQL Server中，无法使用OPENJSON的情况下，可通过以下两种方案逐行提取JSON数组内的元素：

方法一：递归CTE生成索引+JSON_VALUE提取（推荐） #

通过JSON_VALUE获取数组长度，用递归CTE生成从0开始的索引序列，再关联原表逐个定位数组元素，这是兼容性和可靠性最高的方案。

示例代码 #

假设你的JSON结构包含数组$.Entry.AnswersJson.page1.Items，完整SQL如下：

-- 生成数组索引序列的递归CTE
WITH IndexSequence AS (
    SELECT 0 AS idx
    UNION ALL
    SELECT idx + 1
    FROM IndexSequence
    -- 限制索引不超过所有行中最大数组长度-1
    WHERE idx + 1 < (SELECT MAX(JSON_VALUE(Fields, '$.Entry.AnswersJson.page1.Items.length')) FROM Json_Table)
)
SELECT
    JSON_VALUE(j.Fields, '$.Entry.AnswersJson.page1.Date') AS dump_date,
    JSON_VALUE(j.Fields, '$.Entry.AnswersJson.page1.Name') AS Name,
    -- 通过索引定位数组中的单个元素
    JSON_VALUE(j.Fields, '$.Entry.AnswersJson.page1.Items[' + CAST(s.idx AS VARCHAR(10)) + ']') AS ItemValue
FROM Json_Table j
-- 关联索引序列，只取当前行数组长度内的有效索引
JOIN IndexSequence s ON s.idx < JSON_VALUE(j.Fields, '$.Entry.AnswersJson.page1.Items.length')
-- 若数组长度超过100，必须开启递归次数限制解除
OPTION (MAXRECURSION 0);

注意事项 #

• JSON_VALUE(..., '.length')可直接获取JSON数组的元素数量，依赖JSON格式规范；
• 默认递归次数上限为100，若数组长度超过该值，必须添加OPTION (MAXRECURSION 0)；
• 支持嵌套数组，只需增加对应维度的索引序列即可扩展逻辑。

方法二：XML拆分字符串（仅适用于简单元素） #

如果数组元素不含逗号、转义双引号等特殊字符，可将数组字符串转为XML后拆分，得到单行值。

示例代码 #

WITH XmlConversion AS (
    SELECT
        JSON_VALUE(j.Fields, '$.Entry.AnswersJson.page1.Date') AS dump_date,
        JSON_VALUE(j.Fields, '$.Entry.AnswersJson.page1.Name') AS Name,
        -- 将数组字符串转换为XML格式
        CAST(
            '<items><item>' + 
            REPLACE(REPLACE(JSON_QUERY(j.Fields, '$.Entry.AnswersJson.page1.Items'), '["', ''), '"]', '') + 
            '</item></items>' 
            AS XML
        ) AS ItemXml
    FROM Json_Table j
)
SELECT
    dump_date,
    Name,
    -- 从XML节点中提取单个元素值
    x.item.value('.', 'VARCHAR(100)') AS ItemValue
FROM XmlConversion
CROSS APPLY ItemXml.nodes('/items/item') AS x(item);

局限性 #

• 若数组元素包含逗号、转义双引号（如"Apple, Red"），字符串替换会破坏元素结构，导致拆分错误；
• 仅适合结构简单、无特殊字符的JSON数组。

内容的提问来源于stack exchange，提问作者George M