如何用SQL Server的JSON_MODIFY彻底删除JSON数组元素？

问题原因 #

你使用JSON_MODIFY将元素设为null的操作，仅会把目标位置的元素值置空，并不会从数组中移除该元素，因此结果里会保留null占位符，而非直接缩减数组长度。

解决方案 #

方法一：通用方案（适配SQL Server 2016及以上版本） #

通过OPENJSON解析目标数组，过滤掉要删除的元素后重新聚合为新数组，再用JSON_MODIFY替换原数组：

UPDATE MYTABLE
SET MYJSONCOL = JSON_MODIFY(
    MYJSONCOL,
    '$.section[0].subsection[7].myArray',
    JSON_QUERY((
        SELECT STRING_AGG(JSON_QUERY(value), ',') WITHIN GROUP (ORDER BY CONVERT(int, [key]))
        FROM OPENJSON(MYJSONCOL, '$.section[0].subsection[7].myArray')
        WHERE value <> '"two"' -- 注意元素值需带双引号，OPENJSON返回的字符串类型元素会被包裹引号
    ))
)
WHERE MYID = 'ABCD';

• OPENJSON将数组拆分为键值对，key对应数组索引，value是元素的JSON格式字符串
• 过滤逻辑排除目标元素，STRING_AGG将剩余元素拼接为合法的JSON数组字符串
• JSON_QUERY确保拼接后的字符串被识别为JSON对象，避免被自动转义

方法二：SQL Server 2022+简化方案 #

SQL Server 2022新增JSON_ARRAY_REMOVE函数，可直接删除数组指定索引的元素：

UPDATE MYTABLE
SET MYJSONCOL = JSON_MODIFY(
    MYJSONCOL,
    '$.section[0].subsection[7].myArray',
    JSON_ARRAY_REMOVE(JSON_QUERY(MYJSONCOL, '$.section[0].subsection[7].myArray'), 1)
)
WHERE MYID = 'ABCD';

• JSON_ARRAY_REMOVE接收目标数组和要删除的索引参数，直接返回移除元素后的新数组
• JSON_QUERY用于提取原数组的JSON对象，作为JSON_ARRAY_REMOVE的输入源

注意事项 #

• 若数组元素是复杂JSON对象而非简单字符串，过滤条件需调整为JSON_VALUE(value, '$.属性名') <> '目标值'的形式
• 执行更新前建议先用SELECT语句验证生成的新JSON结构，避免误操作

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