PostgreSQL复制符合条件表行并修改JSON字段属性的SQL语法报错问题

你写的SQL核心问题是JSON操作符使用错误：

• 你在SELECT子句中用的->是PostgreSQL的JSON取值操作符，作用是从JSON对象中提取指定键对应的值，右侧参数必须是键名的文本值，不能传入JSON对象，因此signal_key -> '{"signalVersion": "0"}'完全不符合该操作符的语法逻辑，无法实现修改JSON属性的效果。
• 你写的WHERE筛选条件signal_key @> '{"signalVersion": "prod"}'是正确的，该操作符是PostgreSQL的JSON包含判断符，可以正确筛选出signal_key中signalVersion值为prod的行。

要修改JSON对象内的指定属性值，需要使用JSON修改函数jsonb_set（如果你的signal_key是JSONB类型，这也是支持@>操作符的最常用类型），函数语法为jsonb_set(原jsonb对象, 属性路径数组, 新的属性值, 键不存在时是否创建)，默认最后一个参数为true，即键不存在时自动创建，符合你的使用场景。
注意你需要设置的值为0：如果要存数值类型的0，新值传'0'::jsonb即可；如果要和原有值一样存字符串类型的"0"，则需要传'"0"'::jsonb。

修正后的SQL如下：

insert into signals (signal_key, signal_value)
select
  jsonb_set(signal_key, '{signalVersion}', '0'),
  signal_value
from signals
where signal_key @> '{"signalVersion": "prod"}';

如果你的signal_key是JSON类型而非JSONB，PostgreSQL 14及以上版本可以直接把函数替换为json_set使用；更低版本可以先转JSONB处理完成后再转回JSON类型，写法为jsonb_set(signal_key::jsonb, '{signalVersion}', '0')::json。

建议：正式执行插入前，先单独执行SELECT部分查询前10条结果，确认生成的新signal_key属性正确、其他原有字段未丢失，再执行插入操作避免脏数据。

-- 预校验查询
select
  jsonb_set(signal_key, '{signalVersion}', '0') as new_signal_key,
  signal_value
from signals
where signal_key @> '{"signalVersion": "prod"}' limit 10;

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