SQL Server中如何使用JSON_MODIFY移除JSON数组中的元素

嘿，我之前也碰到过这个问题！虽然JSON_MODIFY本身没有直接提供删除数组元素的功能，但我们可以结合SQL Server的其他JSON函数和字符串聚合能力，通过「拆分数组→过滤元素→重组数组」的思路来实现需求，下面给你两种实用的实现方式：

方式一：按元素值移除（比如删除子部门ID=456的元素） #

针对你给出的示例JSON，我们可以先把数组拆成单独的行，过滤掉目标ID，再重新拼接成合法的JSON数组，最后用JSON_MODIFY替换原数组：

DECLARE @json NVARCHAR(MAX) = '{"array":[123,456]}';
DECLARE @removeDeptId INT = 456; -- 要移除的子部门ID

-- 执行移除操作
SET @json = JSON_MODIFY(
    @json,
    '$.array',
    (
        -- 拆分原数组，过滤目标ID后重组为JSON数组
        SELECT JSON_QUERY('[' + STRING_AGG(value, ',') + ']')
        FROM OPENJSON(@json, '$.array')
        WHERE CAST(value AS INT) != @removeDeptId
    )
);

-- 查看结果
SELECT @json AS UpdatedJson;

执行后就能得到你想要的结果：{"array":[123]}

代码小解释： #

• OPENJSON(@json, '$.array')：把JSON数组拆分成多行记录，每行的value就是数组里的单个ID
• WHERE CAST(value AS INT) != @removeDeptId：过滤掉要删除的子部门ID
• STRING_AGG(value, ',')：把剩下的ID拼接成逗号分隔的字符串（比如123）
• JSON_QUERY('[' + ... + ']')：把拼接后的字符串包裹成合法的JSON数组格式，避免JSON_MODIFY自动转义引号
• JSON_MODIFY：用新生成的数组替换原JSON中$.array路径的值

方式二：按数组索引移除（比如删除第2个元素） #

如果你的需求是按位置删除元素（注意JSON数组索引从0开始），可以利用OPENJSON返回的key列（代表数组索引）来过滤：

DECLARE @json NVARCHAR(MAX) = '{"array":[123,456]}';
DECLARE @removeIndex INT = 1; -- 要移除的元素索引（这里对应第2个元素）

SET @json = JSON_MODIFY(
    @json,
    '$.array',
    (
        SELECT JSON_QUERY('[' + STRING_AGG(value, ',') + ']')
        FROM OPENJSON(@json, '$.array')
        WHERE CAST([key] AS INT) != @removeIndex
    )
);

SELECT @json AS UpdatedJson;

这个方法同样会得到{"array":[123]}的结果，适合需要按位置删除的场景。

额外小提示 #

• 如果删除后数组没有剩余元素，结果会是{"array":[]}，完全符合JSON规范
• 这个方法适配多种类型的数组元素，只要调整CAST的类型就能兼容字符串等其他元素类型

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