PostgreSQL 9.6：结合查询更新jsonb列添加新属性

嘿，这个问题我熟！在PostgreSQL里给jsonb列动态添加来自查询的子属性完全可以实现，主要用jsonb_set函数结合子查询或者关联表更新就行，分两种场景给你讲清楚：

场景1：所有行用同一个查询值更新 #

如果你的目标值是从某个查询里拿到的单一值（比如从另一张表取特定记录的字段，或者聚合结果），直接把子查询嵌进UPDATE语句里就行。比如假设你要从another_table中id=1的记录里取value字段作为sub_attribute的值：

UPDATE xyz
SET metadata = jsonb_set(
    metadata,  -- 要修改的原jsonb列
    '{sub_attribute}',  -- 要添加的子属性路径，用大括号包裹
    (SELECT to_jsonb(value_column) FROM another_table WHERE id = 1),  -- 把查询结果转成jsonb类型
    true  -- 如果sub_attribute不存在就创建，存在则覆盖
)
-- 这里可以加WHERE条件，比如只更新特定行：WHERE xyz.id > 10

这里要注意：子查询必须返回单一值，如果查询可能返回多行，记得加LIMIT 1或者用聚合函数（比如MAX(value_column)）确保结果唯一。

场景2：每行对应不同的查询值（关联表更新） #

如果xyz表的每一行需要对应另一张表的不同值（比如通过外键关联），那就用FROM子句关联两张表，实现逐行匹配更新：

比如xyz表有个foreign_id字段，和another_table的id字段关联，要给每行xyz的metadata添加对应another_table的value_column作为sub_attribute：

UPDATE xyz
SET metadata = jsonb_set(
    xyz.metadata,
    '{sub_attribute}',
    to_jsonb(at.value_column),
    true
)
FROM another_table at
WHERE xyz.foreign_id = at.id;  -- 关联条件

实用小提示 #

1. 预览更新结果：执行UPDATE前，先用SELECT预览一下修改后的结果，避免误操作：

SELECT 
    xyz.*,
    jsonb_set(xyz.metadata, '{sub_attribute}', to_jsonb(at.value_column), true) AS updated_metadata
FROM xyz
JOIN another_table at ON xyz.foreign_id = at.id;

2. 只添加不存在的属性：如果不想覆盖已有的sub_attribute，把jsonb_set的第四个参数改成false，或者加个WHERE条件过滤：

UPDATE xyz
SET metadata = jsonb_set(metadata, '{sub_attribute}', (SELECT to_jsonb(value_column) FROM another_table WHERE id=1), false)
WHERE NOT metadata ? 'sub_attribute';  -- 只更新没有sub_attribute的行

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