Postgresql左加入json_agg（）忽略/删除NULL

Question

我使用的是LEFTJOIN，在某些情况下，没有正确的表匹配，因此用空（null）值替换正确的表列。因此，我得到了[null]作为JSON聚合之一。

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":"[e-mail]"},{"id":4,"user_id":1,"email":"[e-mail]"}]
   2  |  Nick  |  [null]

如何忽略/删除空值，以便在右表列为空时我有一个空的 JSON 数组 []？

Answer 1

如果这实际上是一个PostgreSQL错误，我希望它在9.4中得到修复。非常烦人。

SELECT C.id, C.name, 
  COALESCE(NULLIF(json_agg(E)::TEXT, '[null]'), '[]')::JSON AS emails 
FROM contacts C
LEFT JOIN emails E ON C.id = E.user_id
GROUP BY C.id;

我个人不做COALESCE位，只返回NULL。你的电话。

Answer 2

像这样的东西，可能是？

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小提琴演示

你也可以在加入之前分组（我更喜欢这个版本，它有点清晰）：

select
    c.id, c.name,
    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小提琴演示

Answer 3

在9.4中，您可以使用合并和聚合过滤器表达式。

SELECT C.id, C.name, 
  COALESCE(json_agg(E) FILTER (WHERE E.user_id IS NOT NULL), '[]') AS emails 
FROM contacts C
LEFT JOIN emails E ON C.id = E.user_id
GROUP BY C.id, C.name
ORDER BY C.id;

过滤器表达式阻止聚合处理空的行，因为不满足左联接条件，所以最终会得到一个数据库空值，而不是json[null]。一旦数据库为空，就可以像往常一样使用coalesce。

http://www . PostgreSQL . org/docs/9.4/static/SQL-expressions . html #语法-聚合