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

You could use `jsonb_agg(alias) filter (where alias is not null)` — , Aug 31 '21 at 17:02
Note that the `array_agg` isn't (or shouldn't be) removing the `null` value. Try running `\pset null (null)` before running this to see the null value. — Marth, Aug 31 '21 at 17:05
Normally the nulls would come from a left join and I want an empty array for IDs which don't have a match in the joined table WHERE alias not null would remove the rows entirely. — Marcel Joss, Aug 31 '21 at 17:07

score 3 · Accepted Answer · answered Aug 31 '21 at 17:13

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;

PostgreSQL strange behaviour with array_to_json

1 Answers1