Postgresql LEFT JOIN json_agg()忽略/删除NULL

发布时间：2020-12-13 16:39:55 所属栏目：百科来源：网络整理

导读：SELECT C.id,C.name,json_agg(E) AS emails FROM contacts CLEFT JOIN emails E ON C.id = E.user_idGROUP BY C.id; Postgres 9.3创建输出 id | name | emails----------------------------------------------------------- 1 | Ryan | [{"id":3,"user_id":1

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数组[]？

干杯!

这样的事情可能吗？

select
    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

sql fiddle demo

你也可以在加入之前进行分组(我更喜欢这个版本，它有点更清楚)：

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

sql fiddle demo

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!