SQL Server中移除JSON列指定对象并更新同列的实现方法

我来帮你搞定这个需求！要移除JSON数组里attribute为Work的整个对象并更新回原列，我们可以利用SQL Server的JSON处理函数来实现，不管每个对象的属性数量有多少都能适用。

核心思路 #

1. 把JSON数组拆分成单个对象行；
2. 过滤掉attribute等于Work的对象；
3. 将剩下的对象重新组合成新的JSON数组；
4. 把新数组更新回原列。

具体实现代码 #

假设你的表名为YourTable，存储JSON的列名为YourJsonColumn，可以用下面的UPDATE语句：

UPDATE YourTable
SET YourJsonColumn = (
    -- 重新组合过滤后的对象成JSON数组
    SELECT JSON_QUERY(j.[value]) AS [*]
    FROM OPENJSON(YourJsonColumn) AS j
    -- 从每个对象中提取attribute字段用于过滤
    CROSS APPLY OPENJSON(j.[value]) WITH (
        attribute NVARCHAR(50) '$.attribute'
    ) AS attr
    -- 排除attribute为Work的对象
    WHERE attr.attribute <> 'Work'
    FOR JSON PATH
)
-- 只更新确实包含Work对象的行，提升效率
WHERE EXISTS (
    SELECT 1
    FROM OPENJSON(YourJsonColumn) AS j
    CROSS APPLY OPENJSON(j.[value]) WITH (
        attribute NVARCHAR(50) '$.attribute'
    ) AS attr
    WHERE attr.attribute = 'Work'
)

代码细节解释 #

• OPENJSON(YourJsonColumn)：将JSON数组拆分为单独的对象行，每行的[value]就是一个完整的对象JSON字符串；
• CROSS APPLY OPENJSON(j.[value]) WITH (...)：从每个对象中提取attribute字段，不需要关心对象的其他属性；
• JSON_QUERY(j.[value])：确保原对象的JSON结构不会被转义成字符串，保留原本的格式；
• AS [*] FOR JSON PATH：让SQL Server直接将过滤后的对象组合成数组，生成符合要求的JSON格式；
• 最后的WHERE EXISTS条件：避免对不包含Work对象的行做无用更新，优化执行效率。

测试建议 #

在执行UPDATE之前，你可以先运行下面的SELECT语句验证修改结果是否符合预期：

SELECT 
    YourJsonColumn AS 原始JSON,
    (
        SELECT JSON_QUERY(j.[value]) AS [*]
        FROM OPENJSON(YourJsonColumn) AS j
        CROSS APPLY OPENJSON(j.[value]) WITH (
            attribute NVARCHAR(50) '$.attribute'
        ) AS attr
        WHERE attr.attribute <> 'Work'
        FOR JSON PATH
    ) AS 修改后JSON
FROM YourTable
WHERE EXISTS (
    SELECT 1
    FROM OPENJSON(YourJsonColumn) AS j
    CROSS APPLY OPENJSON(j.[value]) WITH (
        attribute NVARCHAR(50) '$.attribute'
    ) AS attr
    WHERE attr.attribute = 'Work'
)

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