Postgres returns [null] instead of [] for array_agg of join table
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.
The docs say that when you are aggregating zero rows, then you get a null value, and the note about using COALESCE
is addressing this specific case.
This does not apply to your query, because of the way a LEFT JOIN
behaves - when it finds zero matching rows, it returns one row, filled with nulls (and the aggregate of one null row is an array with one null element).
You might be tempted to blindly replace [NULL]
with []
in the output, but then you lose the ability to distiguish between objects with no tags and tagged objects where tags.tag
is null. Your application logic and/or integrity constraints may not allow this second case, but that's all the more reason not to suppress a null tag if it does manage to sneak in.
You can identify an object with no tags (or in general, tell when a LEFT JOIN
found no matches) by checking whether the field on the other side of the join condition is null. So in your case, just replace
array_agg(tags.tag)
with
CASE
WHEN taggings.object_id IS NULL
THEN ARRAY[]::text[]
ELSE array_agg(tags.tag)
END
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)