Postgres returns [null] instead of [] for array_agg of join table

Question

I'm selecting some objects and their tags in Postgres. The schema is fairly simple, three tables:

objects id

taggings id | object_id | tag_id

tags id | tag

I'm joining the tables like this, using array_agg to aggregate the tags into one field:

SELECT objects.*,     array_agg(tags.tag) AS tags, FROM objects LEFT JOIN taggings ON objects.id = taggings.object_id LEFT JOIN tags ON tags.id = taggings.tag_id 

However, if the object has no tags, Postgres returns this:

[ null ] 

instead of an an empty array. How can I return an empty array when there are no tags? I have double checked that I don't have a null tag being returned.

The aggregate docs say "The coalesce function can be used to substitute zero or an empty array for null when necessary". I tried COALESCE(ARRAY_AGG(tags.tag)) as tags but it still returns an array with null. I have tried making the second parameter numerous things (such as COALESCE(ARRAY_AGG(tags.tag), ARRAY()), but they all result in syntax errors.

Answer 1

Another option might be array_remove(..., NULL) (introduced in 9.3) if tags.tag is NOT NULL (otherwise you might want to keep NULL values in the array, but in that case, you can't distinguish between a single existing NULL tag and a NULL tag due to the LEFT JOIN):

SELECT objects.*,      array_remove(array_agg(tags.tag), NULL) AS tags, FROM objects LEFT JOIN taggings ON objects.id = taggings.object_id LEFT JOIN tags ON tags.id = taggings.tag_id 

If no tags are found, an empty array is returned.

Answer 2

Since 9.4 one can restrict an aggregate function call to proceed only rows that match a certain criterion: array_agg(tags.tag) filter (where tags.tag is not null)