SQL Server 2019中JSON_MODIFY结合CROSS APPLY无法更新JSON数组元素

核心逻辑 #

通过OPENJSON解析目标JSON数组，匹配指定indexingRecordId的元素并获取其数组索引，再用JSON_MODIFY动态定位路径完成更新。

完整更新脚本 #

假设你的表mytable2包含主键列（示例用id）和存储JSON的列（示例用json_data），需要将indexingRecordId为'target_id'的元素的imageUrl更新为'new_image_path'，脚本如下：

UPDATE t
SET json_data = JSON_MODIFY(
    t.json_data,
    CONCAT('$.documents[', j.[key], '].imageUrl'),
    'new_image_path'
)
FROM mytable2 t
CROSS APPLY OPENJSON(t.json_data, '$.documents') j
WHERE JSON_VALUE(j.value, '$.indexingRecordId') = 'target_id'
-- 若需指定单条记录更新，添加主键过滤
AND t.id = 1;

关键细节 #

• OPENJSON将documents数组拆分为行集，其中[key]对应数组元素的索引（从0开始计数），value为单个元素的JSON对象。
• JSON_VALUE(j.value, '$.indexingRecordId')用于提取每个数组元素的indexingRecordId，实现精准匹配。
• CONCAT动态拼接JSON路径，确保JSON_MODIFY能定位到目标元素的imageUrl字段，解决索引位置不固定的问题。
• 若你的JSON列是JSON类型（而非NVARCHAR(MAX)），脚本无需调整，直接适用。

验证更新结果 #

执行更新后，可通过以下语句确认修改是否生效：

SELECT json_data FROM mytable2 WHERE id = 1;

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