Postgresql LEFT JOIN json_agg() ignore/remove NULL

Question

I am using a LEFT JOIN there will be cases where there is no right-table match therefore empty (null) values are substituted for the right-table columns. As a result I am getting [null] as one of the JSON aggregates.

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 creates output for example

  id  |  name  |  emails
-----------------------------------------------------------
   1  |  Ryan  |  [{"id":3,"user_id":1,"email":"[email protected]"},{"id":4,"user_id":1,"email":"[email protected]"}]
   2  |  Nick  |  [null]

How can I ignore/remove null so I have an empty JSON array [] when the right-table column is null?

Answer 1

In 9.4 you can use coalesce and an aggregate filter expression.

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;

The filter expression prevents the aggregate from processing the rows that are null because the left join condition is not met, so you end up with a database null instead of the json [null]. Once you have a database null, then you can use coalesce as usual.

http://www.postgresql.org/docs/9.4/static/sql-expressions.html#SYNTAX-AGGREGATES

Answer 2

something like this, may be?

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

you also can group before join (I'd prefer this version, it's a bit more clear):

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 fiddle demo

Answer 3

If this is actually a PostgreSQL bug, I hope it's been fixed in 9.4. Very annoying.

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;

I personally don't do the COALESCE bit, just return the NULL. Your call.