如何用SQL REPLACE同时重命名JSON键、清空值并保留指定值？

问题背景 #

现有JSON字段内容：

[{"header":"C", "value": 1},{"header":"D", "value": 2},{"header":"E", "value": 3}]

需要将header键重命名为test，同时将test的值设为空字符串，最终得到：

[{"test":"", "value": 1},{"test":"", "value":2},{"test":"", "value": 3}]

当前仅能通过REPLACE修改键名，询问能否用REPLACE实现完整需求，以及如何在修改时保留value值不变。

能否用REPLACE函数实现？ #

可以，但仅适用于JSON格式完全固定、无特殊字符的场景——因为REPLACE是纯字符串替换，无法识别JSON结构，容易出现误替换或失效的情况：

1. 精准字符串替换（仅适配固定值） #

如果header的取值是固定的（比如示例中的"C"、"D"、"E"），可以直接逐个替换：

UPDATE Files 
SET Columns = REPLACE(
    REPLACE(
        REPLACE(Columns, '"header":"C"', '"test":""'),
        '"header":"D"', '"test":""'
    ),
    '"header":"E"', '"test":""'
);

缺点：扩展性极差，header值一旦变化就需要修改SQL。

2. 正则替换（适配任意字符串值，需数据库支持） #

如果数据库支持正则替换（如MySQL 8.0+、PostgreSQL），可以匹配"header":"xxx"的通用模式，一次性替换：

MySQL 8.0+ 示例：

UPDATE Files 
SET Columns = REGEXP_REPLACE(Columns, '"header":"[^"]+"', '"test":""');

PostgreSQL 示例：

UPDATE Files 
SET Columns = regexp_replace(Columns::text, '"header":"[^"]+"', '"test":""', 'g')::jsonb;

这种方式能处理任意双引号包裹的header值，但仍需保证JSON格式规范（比如值中无未转义的双引号）。

更可靠的方案：原生JSON函数（推荐） #

用数据库原生的JSON操作函数修改，完全基于JSON结构处理，不会破坏格式，且能稳定保留value值：

MySQL 8.0+ 示例 #

UPDATE Files 
SET Columns = (
    SELECT JSON_ARRAYAGG(
        JSON_SET(JSON_REMOVE(item, '$.header'), '$.test', '')
    )
    FROM JSON_TABLE(Columns, '$[*]' COLUMNS (item JSON PATH '$')) AS jt
)
WHERE Columns IS NOT NULL;

SQL Server 示例 #

UPDATE f
SET Columns = (
    SELECT '[' + STRING_AGG('{"test":"", "value":' + CAST(value AS VARCHAR(MAX)) + '}', ',') + ']'
    FROM OPENJSON(f.Columns) WITH (value INT '$.value')
)
FROM Files f;

PostgreSQL 示例 #

UPDATE Files 
SET Columns = (
    SELECT jsonb_agg(
        jsonb_set(item - 'header', '{test}', '""'::jsonb)
    )
    FROM jsonb_array_elements(Columns::jsonb) AS item
);

关于保留value值的说明 #

上述所有方案都不会修改value字段：

• 字符串/正则替换仅匹配header相关的键值对，完全跳过value的内容；
• 原生JSON函数仅移除header键，添加空值的test键，value键值对始终保持原样。

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