SQL Server中通过迭代为JSON数组元素拼接值的实现方案

不需要创建临时列，直接利用OPENJSON解析JSON数组元素，拼接前缀后重新生成目标JSON数组即可，具体实现如下：

1. 先验证转换结果 #

执行以下查询查看转换后的格式是否符合要求：

SELECT 
    (
        -- 处理Klaus对应的数组，拼接01前缀
        SELECT CONCAT('01', value) AS [value]
        FROM OPENJSON(d.MyColumn, '$.Klaus')
        UNION ALL
        -- 处理Herbert对应的数组，拼接02前缀
        SELECT CONCAT('02', value) AS [value]
        FROM OPENJSON(d.MyColumn, '$.Herbert')
        FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    ) AS NewMyColumn
FROM databasetable d
WHERE d.MyColumn IS NOT NULL AND d.MyColumn != '{}'

2. 直接更新原列 #

如果验证结果正确，执行以下语句直接更新MyColumn到目标格式：

UPDATE databasetable
SET MyColumn = (
    SELECT CONCAT('01', value) AS [value]
    FROM OPENJSON(MyColumn, '$.Klaus')
    UNION ALL
    SELECT CONCAT('02', value) AS [value]
    FROM OPENJSON(MyColumn, '$.Herbert')
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
)
WHERE MyColumn IS NOT NULL AND MyColumn != '{}'

说明 #

• OPENJSON(MyColumn, '$.Klaus')会将Klaus对应的JSON数组拆分成行级数据，每一行的value就是数组中的单个元素
• CONCAT('01', value)完成前缀拼接
• UNION ALL合并两个关键字对应的所有拼接后元素
• FOR JSON PATH, WITHOUT_ARRAY_WRAPPER将合并后的行数据重新组装成JSON数组，正好匹配你需要的目标格式

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