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) — user330315
– user330315, Commented Aug 31, 2021 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
– Marth, Commented Aug 31, 2021 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
– Marcel Joss, Commented Aug 31, 2021 at 17:07

Marcel Joss · Accepted Answer · 2021-08-31 17:13:24Z

3

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;

answered Aug 31, 2021 at 17:13

Marcel Joss

434 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL strange behaviour with array_to_json

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related