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 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
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
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.