PostgreSQL 12 中无需子查询构建带用户名的jsonb对象的实现方法

你当前的嵌套派生表写法本质上是先拆解jsonb数组、再做自表关联取用户名，这套逻辑完全可以通过LATERAL JOIN简化，去掉冗余的子查询嵌套，之前尝试失败基本都是写法细节或者类型匹配的问题。

单用户场景（原示例：筛选uid=2的用户history处理） #

直接可运行的简化写法如下，无任何嵌套派生表：

SELECT jsonb_agg(elem || jsonb_build_object('uname', u2.uname))
FROM users u1
CROSS JOIN LATERAL jsonb_array_elements(u1.history) AS j(elem)
LEFT JOIN users u2 ON (j.elem->>'uid')::int = u2.uid
WHERE u1.uid = 2;

实现逻辑说明：

• jsonb_array_elements是返回多行的表函数，放在LATERAL子句中可以直接对左侧主表（u1，也就是待处理的用户行）逐行执行数组拆解，生成独立的json对象元素行，不需要单独套子查询完成拆分组装
• 关联条件显式把->>'uid'取出的文本值转成整数，匹配users表的uid字段类型，避免类型不兼容报错
• 用jsonb_agg替代普通json_agg，聚合结果可以直接和原history字段的jsonb类型对齐，减少后续处理的类型转换成本

批量全表处理场景 #

如果需要一次性为所有用户的history数组补全uname字段，只需要在上述逻辑基础上增加按用户主键分组即可，依然保持无嵌套子查询的结构：

SELECT 
  u1.uid,
  jsonb_agg(elem || jsonb_build_object('uname', u2.uname)) AS updated_history
FROM users u1
CROSS JOIN LATERAL jsonb_array_elements(u1.history) AS j(elem)
LEFT JOIN users u2 ON (j.elem->>'uid')::int = u2.uid
GROUP BY u1.uid;

常见踩坑点（导致LATERAL尝试失败的核心原因） #

• 误将LATERAL子句强制要求写为独立SELECT块的派生表：实际上只要是返回多行的表函数，不需要套嵌套查询就能直接接在LATERAL后面和左侧主表做隐式关联
• 关联时未处理类型匹配：jsonb字段用->>取出的uid值是文本类型，直接和整数类型的uid字段关联会触发类型不兼容错误
• 未处理空值兼容场景：如果原history字段为空数组、值为NULL，CROSS JOIN LATERAL会因为表函数返回0行过滤掉对应主表记录，此时需要将拆表函数的关联改为左连接，搭配COALESCE补全空数组：

需要兼容空history的写法示例：

SELECT 
  u1.uid,
  COALESCE(jsonb_agg(elem || jsonb_build_object('uname', u2.uname)), '[]'::jsonb) AS updated_history
FROM users u1
LEFT JOIN LATERAL jsonb_array_elements(u1.history) AS j(elem) ON true
LEFT JOIN users u2 ON (j.elem->>'uid')::int = u2.uid
GROUP BY u1.uid;

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