SELECT C.id,C.name,json_agg(E) AS emails FROM contacts C LEFT JOIN emails E ON C.id = E.user_id GROUP BY C.id;
Postgres 9.3创建输出
id | name | emails
-----------------------------------------------------------
1 | Ryan | [{"id":3,"user_id":1,"email":"hello@world.com"},{"id":4,"email":"again@awesome.com"}]
2 | Nick | [null]
当我使用LEFT JOIN时，会出现没有右表匹配的情况，因此空(空)值代替右表列。因此，我将[null]作为JSON聚合之一。
如何忽略/删除null，因此当右列列为空时，我有一个空的JSON数组[]？
干杯!
这样的事情可能吗？
原文链接：https://www.f2er.com/postgresql/193117.htmlselect
c.id,c.name,case when count(e) = 0 then '[]' else json_agg(e) end as emails
from contacts as c
left outer join emails as e on c.id = e.user_id
group by c.id
你也可以在加入之前进行分组(我更喜欢这个版本，它有点更清楚)：
select
c.id,coalesce(e.emails,'[]') as emails
from contacts as c
left outer join (
select e.user_id,json_agg(e) as emails from emails as e group by e.user_id
) as e on e.user_id = c.id
