Postgresql LEFT JOIN json_agg() ignore/remove NULL
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]
As 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.
How can I ignore/remove null so I have an empty JSON array [] when the right-table column is null?
Cheers!
Answer
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