Postgres join jsonb column

Question

我有一个左表，看起来像这个

+-----------+-----------------------+
| name      | interests             |
+-----------+-----------------------+
| Jason     | ["sports", "food"]    |
+-----------+-----------------------+

还有另一个有兴趣信息的表。

+-----------+----------------------------+
| interest  | items                      |
+-----------+----------------------------+
| sports    | ["football", "swimming"]   |
+-----------+----------------------------+
| food      | ["pasta", "bread"]         |
+-----------+----------------------------+
| news      | ["BBC", "New York Times"]  |
+-----------+----------------------------+

我现在如何进行查询，以便获得这样的输出？基本上就像在Python中一样，我们会迭代interests，并得到所有的items都属于这些interests。

非常感谢。

+-----------+---------------------------------------------+
| name      | items                                       |
+-----------+---------------------------------------------+
| Jason     | ["football", "swimming", "pasta", "bread"]  |
+-----------+---------------------------------------------+

Answer 1

这确实是一个有问题的数据库设计。传统的多对多餐桌可能更有意义。

但是，为了实现您想要的，您需要为person表中的所有兴趣取消测试interest表中的全部元素。然后将它们聚合回JSON数组：

select p.name,
i.interests
from person p 
left join lateral (
select jsonb_agg(ix.item) as interests
from interest i
cross join jsonb_array_elements(i.items) as ix(item)
where p.interests ? i.interest
) as i on true

在线示例

---

通过定义自己的聚合来附加多个jsonb值(而不是像jsonb_agg()那样在"原始"数组上创建数组(，可以实现稍微紧凑的版本。

create aggregate jsonb_append_agg(jsonb) 
(
sfunc = jsonb_concat(jsonb, jsonb),
stype = jsonb
);

然后你可以这样使用它：

select p.name,
i.interests
from person p 
left join lateral (
select jsonb_append_agg(i.items) as interests
from interest i
where p.interests ? i.interest
) as i on true;