PostgreSQL中如何实现递归JSONB关联拼接为单个JSON字段

该需求完全可实现，你之前选用jsonb_array_elements_text属于方案选型错误，该函数仅用于拆解JSONB数组为多行值，不适用于当前同表JSONB字段关联+对象合并的场景。

你的表内实际存储了两类实体数据，均通过JSONB内的building_id字段做关联：

• 楼栋实体：JSONB包含building_name字段，是关联的主端
• 人员实体：JSONB包含full_name、calary字段，是关联的从端
  只需要做同表自连接，匹配两端building_id值，再通过PostgreSQL原生JSONB合并操作符将两类实体的属性合并为单个JSONB对象即可。

假设你的表名为entity_table，可直接执行以下查询得到结果：

SELECT
  person.id,
  building.data || person.data AS data
FROM entity_table person
INNER JOIN entity_table building
  -- 按JSONB内的building_id做关联匹配，转文本比较避免类型隐式转换问题
  ON person.data ->> 'building_id' = building.data ->> 'building_id'
WHERE
  -- 筛选主表数据为人员实体：包含full_name字段
  person.data ? 'full_name'
  -- 筛选关联表数据为楼栋实体：包含building_name字段
  AND building.data ? 'building_name';

• ->>操作符：提取JSONB指定键的值并转为text类型，用于关联条件的等值匹配
• ?操作符：判断JSONB对象是否存在指定顶级键，用于快速区分实体类型，相比->> 'xxx' is not null判断效率更高，支持GIN索引加速
• ||操作符：PostgreSQL原生JSONB合并操作符，会将两个JSONB对象的所有键值对合并为新对象，若存在重名键，靠右的对象值会覆盖靠左的对象值；你的场景中两类实体无冲突字段（building_id两边值一致，覆盖无影响），可直接使用

如果表数据量较大，可以通过建索引大幅提升关联查询速度：

-- 建表达式索引，加速building_id的关联匹配
CREATE INDEX idx_entity_building_id ON entity_table ((data ->> 'building_id'));
-- 建GIN索引，加速JSONB键存在性判断、属性查询
CREATE INDEX idx_entity_data_gin ON entity_table USING GIN (data);

你给出的期望输出存在几处笔误：

1. 结果ID列应为原表的3、4、5，对应三个人员实体，原表ID1、2是楼栋实体不会作为主表行返回
2. 原表中building_id=2的楼栋下没有关联人员，Anna Birkin所属楼栋是building_id=1的Office 1，示例中把其归到Office 2下属于逻辑错误
3. 第三条结果多了一个冗余逗号，属于JSON格式错误

实际查询返回的正确结果如下：

如果需要把没有关联人员的楼栋也返回，可以把INNER JOIN改成LEFT JOIN，对应调整WHERE条件即可。

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