如何在SQL Server中按条件删除JSON数组字典中的指定键

针对Test表Value列中存储的JSON数据，我们需要删除columnState数组里colId为"c"的对象中的sort键，以下是具体实现方案：

方案说明 #

核心思路是解析JSON数组，对符合条件的元素修改后重新拼接成完整JSON，适配数组元素数量、顺序变化的场景。

1. 查询处理后的结果（SQL Server 2017+） #

使用STRING_AGG简化数组元素拼接：

DECLARE @Test table
(
    id int,
    Value VARCHAR(1000)
)
INSERT INTO @Test VALUES
    (1, '{"type": "classic", "columnState": [ {"colId": "a", "sort": "asc"}, {"colId": "b", "sort": "asc"}, {"colId": "c", "sort": "asc"}]}'),
    (2, '{"type": "classic", "columnState": [ {"colId": "a", "sort": "asc"}, {"colId": "c", "sort": "asc"}, {"colId": "b", "sort": "asc"}]}'),
    (3, '{"type": "classic", "columnState": [ {"colId": "a", "sort": "asc"}, {"colId": "b", "sort": "asc"}, {"colId": "c"}]}')

-- 查询处理后的结果
SELECT 
    t.id,
    JSON_QUERY(
        '{"type": "' + j.type + '", "columnState": [' + 
        STRING_AGG(
            CASE 
                WHEN cs.colId = 'c' THEN JSON_MODIFY(cs.value, '$.sort', NULL)
                ELSE cs.value
            END,
            ','
        ) + 
        ']}'
    ) AS UpdatedValue
FROM @Test t
CROSS APPLY OPENJSON(t.Value) WITH (
    type VARCHAR(50) '$.type',
    columnState NVARCHAR(MAX) '$.columnState' AS JSON
) j
CROSS APPLY OPENJSON(j.columnState) cs
GROUP BY t.id, j.type

2. 更新原表数据（SQL Server 2017+） #

直接将处理后的JSON写回原表：

UPDATE t
SET Value = JSON_QUERY(
    '{"type": "' + j.type + '", "columnState": [' + 
    STRING_AGG(
        CASE 
            WHEN cs.colId = 'c' THEN JSON_MODIFY(cs.value, '$.sort', NULL)
            ELSE cs.value
        END,
        ','
    ) + 
    ']}'
)
FROM @Test t
CROSS APPLY OPENJSON(t.Value) WITH (
    type VARCHAR(50) '$.type',
    columnState NVARCHAR(MAX) '$.columnState' AS JSON
) j
CROSS APPLY OPENJSON(j.columnState) cs
GROUP BY t.id, j.type, t.Value

-- 查看更新后的数据
SELECT * FROM @Test

3. 兼容SQL Server 2016及更早版本 #

若使用2016或更早版本（无STRING_AGG函数），改用FOR XML PATH拼接数组：

SELECT 
    t.id,
    JSON_QUERY(
        '{"type": "' + j.type + '", "columnState": [' + 
        STUFF(
            (
                SELECT ',' + CASE 
                                WHEN cs.colId = 'c' THEN JSON_MODIFY(cs.value, '$.sort', NULL)
                                ELSE cs.value
                            END
                FROM OPENJSON(j.columnState) cs
                FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)'),
            1, 1, ''
        ) + 
        ']}'
    ) AS UpdatedValue
FROM @Test t
CROSS APPLY OPENJSON(t.Value) WITH (
    type VARCHAR(50) '$.type',
    columnState NVARCHAR(MAX) '$.columnState' AS JSON
) j

关键函数说明 #

• OPENJSON：解析JSON字符串，提取指定字段或数组
• JSON_MODIFY：修改JSON内容，将指定键设为NULL即可删除该键
• STRING_AGG/FOR XML PATH：将处理后的数组元素重新拼接成JSON数组格式
• JSON_QUERY：确保生成的字符串被识别为JSON类型，避免自动转义特殊字符

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