PostgreSQL中如何用类JOIN语句合并嵌套JSONB字段数组？

在PostgreSQL中，结合JOIN操作和jsonb系列函数，完全能把两张表的数据按需求合并。我先假设你的表结构（如果实际结构有差异，调整对应字段即可），然后给你两种常见场景的实现方案：

假设的表结构 #

• BOOKS表：包含book_id(主键)、title、authors(jsonb类型，存储作者ID数组，示例值：[{"author_id": 1}, {"author_id": 2}])
• AUTHORS表：包含author_id(主键)、author_name

方案1：合并为带完整信息的jsonb数组 #

这个方案会把匹配到的作者信息重新聚合成jsonb数组，保留ID和名字等字段：

SELECT 
    b.book_id,
    b.title,
    jsonb_agg(jsonb_build_object('author_id', a.author_id, 'author_name', a.author_name)) AS authors
FROM BOOKS b
-- 把books表的authors数组拆成单行记录
JOIN LATERAL jsonb_array_elements(b.authors) AS auth(author_obj)
    ON TRUE
-- 关联authors表匹配对应作者
JOIN AUTHORS a
    ON (auth.author_obj->>'author_id')::int = a.author_id
-- 按书籍分组聚合
GROUP BY b.book_id, b.title;

关键步骤说明： #

• jsonb_array_elements(b.authors)：把每个书籍的authors数组拆分成独立的行，每行对应一个作者的json对象
• LATERAL：保证每一行书籍都能正确拆分自己的authors数组，不会出现不匹配的情况
• jsonb_agg：把匹配到的作者信息重新聚合成jsonb数组，还原成类似原数组的结构但补充了完整信息

方案2：合并为逗号分隔的作者名字字符串 #

如果只需要作者名字的列表，不需要json格式，可以用字符串聚合：

SELECT 
    b.book_id,
    b.title,
    string_agg(a.author_name, ', ') AS authors
FROM BOOKS b
JOIN LATERAL jsonb_array_elements(b.authors) AS auth(author_obj)
    ON TRUE
JOIN AUTHORS a
    ON (auth.author_obj->>'author_id')::int = a.author_id
GROUP BY b.book_id, b.title;

这个结果里的authors字段会是类似"张三, 李四"的字符串格式，适合展示场景。

特殊场景适配：如果authors数组直接存作者名字 #

要是你的BOOKS表authors数组是直接存名字的（比如["张三", "李四"]），那可以用jsonb_array_elements_text直接提取字符串，再关联AUTHORS表补充信息：

SELECT 
    b.book_id,
    b.title,
    jsonb_agg(jsonb_build_object('author_name', a.author_name, 'birth_year', a.birth_year)) AS authors
FROM BOOKS b
JOIN LATERAL jsonb_array_elements_text(b.authors) AS auth(author_name)
    ON TRUE
JOIN AUTHORS a
    ON auth.author_name = a.author_name
GROUP BY b.book_id, b.title;

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