在PostgreSQL中将嵌套的JSONB字段的ARRAY与类似JOIN的语句合并? [英] Merge nested ARRAY of JSONB fields with a JOIN-like statement in PostgreSQL?
本文介绍了在PostgreSQL中将嵌套的JSONB字段的ARRAY与类似JOIN的语句合并?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
给出此表:
书
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函数合并作者的键数组元素来获得此结果?
Is it possible to obtain this result, by merging the authors key array elements, with a JOIN-like statement or using jsonb functions?
预订查询结果
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'}] }
谢谢
推荐答案
select b.data || jsonb_build_object('authors', jsonb_agg(a.data ||
jsonb_build_object('id', a.id)))
from books b
left join (select id, jsonb_array_elements(data->'authors') from books) ba on
ba.id = b.id
left join authors a on a.id = ba.id
group by b.id;
这篇关于在PostgreSQL中将嵌套的JSONB字段的ARRAY与类似JOIN的语句合并?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文