[SOLVED] Postgres returns [null] instead of [] for array

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

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.

Solution

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)