PostgreSQL：能否将select语句作为jsonb_set的第三个参数？

我有一张包含jsonb类型列的表，需要将所有记录的JSON结构迁移至新格式：

• 旧结构：{"foo": "bar"}（bar的值随记录不同而变化）
• 新结构：{"foo": {"value": "bar"}}（需保留原foo字段的实际值）

尝试执行以下SQL语句迁移时触发语法错误：

WITH data AS (
  SELECT id AS id, col -> 'foo' AS foo
  FROM t
) UPDATE t SET col = jsonb_set('{"foo": {"value": ""}}', '{foo,value}', SELECT foo FROM data WHERE data.id = t.id);

PostgreSQL 11.21返回错误：

ERROR:  syntax error at or near "SELECT"
LINE 4: ...jsonb_set('{"foo": {"value": ""}}', '{foo,value}', SELECT foo...

请问是否可以将SELECT语句的结果作为jsonb_set函数的第三个参数使用？

不能直接在jsonb_set的参数位置嵌套SELECT子句，这属于语法错误。实际上无需使用CTE或子查询，直接引用当前行的字段即可完成迁移，以下是两种可行方案：

方案一：直接构造目标JSON结构 #

用jsonb_build_object直接生成新格式的JSON，写法更简洁：

UPDATE t
SET col = jsonb_build_object('foo', jsonb_build_object('value', col -> 'foo'));

方案二：修正jsonb_set的用法 #

如果坚持使用jsonb_set，直接将当前行的col -> 'foo'作为第三个参数传入即可，无需额外查询：

UPDATE t
SET col = jsonb_set('{"foo": {"value": ""}}'::jsonb, '{foo,value}', col -> 'foo');

关键说明 #

• col -> 'foo'返回的是原JSON中foo字段的jsonb类型值，可以直接作为jsonb_set的第三个参数（该参数要求为jsonb类型）。
• 两种方案都能批量将所有记录的JSON结构转换为新格式，且完整保留每条记录的foo字段原值。

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