arrays - 在 PostgreSQL 中使用类似 JOIN 的语句合并嵌套的 JSONB 字段数组？

Question

给定这个表:

书籍

 id | data
----+-------------------------------------------------------
 1  | { title: 'Book 1', price: 10.5, authors: [{ id: 1}, { id: 2 }]}
 2  | { title: 'Book 2', price: 11.5, authors: [{ id: 2 } }

作者

 id | data
-----+-------------------------------------------------------
 1  | { name: 'Author 1', address: 'Address author 1' }
 2  | { name: 'Author 2', address: 'Address author 2' }

是否有可能通过将作者的关键数组元素与类似 JOIN 的语句或使用 jsonb 函数合并来获得此结果？

图书查询结果

 id | data
----+------------------------------------------------------------------------------------------------------
 1  | { title: 'Book 1', price: 10.5, authors: [{ id: 1, name: 'Author 1', address: 'Address author 1' }, { id: 2, name: 'Author 2', address: 'Address author 2'}] }
 2  | { title: 'Book 2', price: 11.5, authors: [{ id: 2, name: 'Author 2', address: 'Address author 2'}] }

谢谢

Answer 1

id 数组必须取消嵌套，结果必须再次聚合，这使得查询更加复杂。我已将数组结构更改为更符合逻辑。

select b.data || jsonb_build_object('authors', jsonb_agg(a.data || jsonb_build_object('id', a.id)))
from books b
cross join jsonb_array_elements_text(b.data->'authors')
join authors a on a.id = value::int
group by b.id;

SqlFiddle