PostgreSQL strange behaviour with array_to_json

Question

I wanted to use array_agg to eliminate nulls before converting to JSON but the null reappears in the JSON output. Here is a minimal example demonstrating the behaviour:

select id, array_agg(alias), array_to_json(array_agg(alias))
from (values (1, 'foo'), (1, 'bar'), (2, null)) t(id, alias)
group by id;

The resultset is this:

id|array_agg|array_to_json|
--+---------+-------------+
 1|{foo,bar}|["foo","bar"]|
 2|{}       |[null]       |

Answer 1

The documentation for array_agg states that it "Collects all the input values, including nulls, into an array." The array appearing empty is simply how the output is formatted, but in reality it still contains null. https://www.postgresql.org/docs/current/functions-aggregate.html

To get an empty array for the null values use json_agg with a filter clause and coalesce:

select 
  id,
  coalesce(json_agg(alias) filter (where alias is not null), '[]'::json)
from (values (1, 'foo'), (1, 'bar'), (2, null)) t(id, alias)
group by id;