使用postgres数组字段的内部连接

Question

我们有一个使用rails，react和postgresql的CMS。我们有页面和片断存储在我们的表格中。 每个页面由一组片段组成（一个数组字段）。

我们有可以跨多页使用的作品。

比方说，我们正在呈现PAGE_ID 50806。我们的反应前端需要以下格式的数据。

pieces: [ 
{id: B1fu5jthb, included_on_pages: [50808, 50806]}, 
{id: BJTNssF2Z, included_on_pages: [50808]} 
] 

所以目前，找included_on_pages，我写一个语句来抓取页面的各个部分，然后遍历每一块找到其中包含的特定部分页面。

（基本上是N + 1查询。）

select pieces from page_pieces where page_id = 50806

Looping over each piece

select page_id from page_pieces where 'B1fu5jthb' = any(page_pieces.pieces);

所以我的问题， 相反的循环每一块，发现其包含的页面，我们可以编写一个单一的join statements来获取所有的作品及其included_on_pages

Answer 1

我觉得unnesting的组合，ANY比较和阵列整合应该工作：

with 
    pcs as (select unnest(pieces) as id from page_pieces where page_id = 50806) 
select id, array_agg(page_id) as included_on_pages 
from pcs inner join page_pieces on id = any(pieces) 
group by id; 

See it on SQL Fiddle