为何使用jsonb_set更新jsonb列时仅更新结果集首行？

你遇到的这个问题，核心原因是PostgreSQL的UPDATE ... FROM语句在目标行匹配多个源行时，不会累加更新字段值，而是每次都基于目标行的原始值进行修改。

在你的场景里，同一个posts行匹配了两条reactions记录，数据库会对这条posts行执行两次独立的UPDATE操作：

• 第一次操作：基于原始的空a_to_b，用jsonb_set添加user1的随机值，此时a_to_b变成{"user1": 0.xxx}
• 第二次操作：**再次基于原始的空a_to_b**执行修改，这会覆盖第一次的结果（或者数据库随机选择其中一行生效，取决于执行计划），最终导致只有一个键值对被保留。

要实现将同一个帖子的所有reactions对应的用户名和随机值合并到a_to_b字段中，你需要先对reactions按post_id聚合，生成完整的JSONB对象，再一次性更新posts表：

UPDATE posts p
SET a_to_b = agg_reactions
FROM (
  SELECT 
    post_id,
    jsonb_object_agg(username::text, to_jsonb(random())) AS agg_reactions
  FROM reactions
  GROUP BY post_id
) r
WHERE p.id = r.post_id;

方案生效逻辑 #

• 子查询r会先把每个post_id对应的所有username和随机值聚合为一个完整的JSONB对象
• 随后UPDATE语句用这个聚合后的对象一次性更新posts的a_to_b字段，每个posts行只会被更新一次，自然就能包含所有对应的键值对

如果你需要在现有a_to_b的基础上追加新的键值对（而非直接覆盖），可以把SET子句调整为：

SET a_to_b = COALESCE(p.a_to_b, '{}'::jsonb) || agg_reactions

这样会保留原有的键值对，同时合并新的聚合结果（若存在重复键，新值会覆盖旧值）。

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