Postgresql LEFT JOIN json_agg() ignore/remove NULL

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 CLEFT JOIN emails E ON C.id = E.user_idGROUP BY C.id, C.nameORDER 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

something like this, may be?

select    c.id, c.name,    case when count(e) = 0 then '[]' else json_agg(e) end as emailsfrom contacts as c    left outer join emails as e on c.id = e.user_idgroup 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 emailsfrom 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

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 CLEFT JOIN emails E ON C.id = E.user_idGROUP BY C.id;

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