PostgreSQL中能否用类JOIN语句或JSONB函数合并嵌套JSONB字段及表关联键？

当然可以！PostgreSQL的JSONB功能简直是处理半结构化数据的利器，不仅能实现类JOIN逻辑来合并嵌套JSONB字段，还能把常规的关系表JOIN和JSONB函数结合起来，完美满足你说的BOOKS与AUTHORS表的关联需求。咱们一步步说清楚：

虽然JSONB是嵌套结构，但PostgreSQL提供了一系列函数可以把嵌套的数组或对象「展开」成关系型的行，之后就能用熟悉的JOIN语法来关联匹配了。常见的操作思路：

• 把JSONB数组拆成多行：用jsonb_array_elements()
• 把JSONB对象的键值对拆成多行：用jsonb_each()
• 展开后就可以像操作普通表一样，和其他表（甚至是另一个展开后的JSONB结果）做JOIN，最后再用jsonb_agg()或jsonb_object_agg()把结果重新聚合回JSONB结构。

先假设咱们的表结构是这样的（你可以根据实际情况调整）：

• BOOKS表：id(INT), title(VARCHAR), author_ids(JSONB) —— 这里author_ids是存作者ID的数组，比如[1, 3]
• AUTHORS表：id(INT), name(VARCHAR), bio(TEXT)

场景1：把BOOKS的作者ID数组关联AUTHORS，合并成完整的作者信息JSON数组 #

想要的结果是每本书对应一个包含所有作者详情的JSONB数组，你可以这么写：

SELECT
  b.id,
  b.title,
  jsonb_agg(
    jsonb_build_object(
      'id', a.id,
      'name', a.name,
      'bio', a.bio
    )
  ) AS authors
FROM books b
-- 先把JSONB数组拆成单行的作者ID
JOIN jsonb_array_elements(b.author_ids) AS aid(id)
  ON true
-- 关联AUTHORS表匹配作者信息
JOIN authors a ON (aid.id::INT = a.id)
GROUP BY b.id, b.title;

场景2：如果BOOKS里存的是单个作者的JSONB对象（比如{"id":1, "name":"佚名"}），替换成AUTHORS的完整信息 #

要是你想把BOOKS里的简略作者对象替换成AUTHORS表的完整数据，可以用jsonb_set()来合并：

SELECT
  b.id,
  b.title,
  jsonb_set(b.author, '{}', to_jsonb(a)) AS author
FROM books b
JOIN authors a ON (b.author ->> 'id')::INT = a.id;

场景3：合并两个JSONB结构（PostgreSQL 12+支持） #

如果需要把AUTHORS的信息直接合并到BOOKS的某个JSONB字段里（比如BOOKS有个metadata字段，要把作者信息加进去），可以用jsonb_merge()：

SELECT
  b.id,
  b.title,
  jsonb_merge(b.metadata, to_jsonb(a)) AS metadata
FROM books b
JOIN authors a ON (b.metadata -> 'author' ->> 'id')::INT = a.id;

总的来说，PostgreSQL把关系型数据库的严谨性和JSONB的灵活性结合得非常好，不管是嵌套数组还是单个JSON对象，都能通过「拆开展开→关联→聚合合并」的思路实现类JOIN的效果。

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