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.

Answers


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.


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)


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

I found out that this will do it:

COALESCE(ARRAY_AGG(tags.tag), ARRAY[]::TEXT[])

...assuming that tags.tag is a text type.

Not sure if maybe this would not work in older Postgres versions, but I am using it in ver. 9.6 and it seems to be working and less cumbersome than the CASE WHEN x IS NULL... GROUP BY... solution provided earlier.


The documentation says that an array containing NULL is returned. If you want to convert that to an empty array, then you need to do some minor magic:

SELECT objects.id,
    CASE WHEN length((array_agg(tags.tag))[1]) > 0
    THEN array_agg(tags.tag) 
    ELSE ARRAY[]::text[] END AS tags
FROM objects
LEFT JOIN taggings ON objects.id = taggings.object_id
LEFT JOIN tags ON tags.id = taggings.tag_id
GROUP BY 1;

This assumes that the tags are of text type (or any of its variants); modify the cast as required.

The trick here is that the first (and only) element in a [NULL] array has a length of 0, so if any data is returned from tags you return the aggregate, otherwise construct an empty array of the right type.

Incidentally, the statement in the documentation about using coalesce() is a bit crummy: what is meant is that if you do not want NULL as a result, you can use coalesce() to turn that into a 0 or some other output of your choosing. But you need to apply that to the array elements instead of the array, which, in your case, would not provide a solution.


Need Your Help

RecyclerView store / restore state between activities

android listview android-recyclerview

I'm migrating my ListViews to RecyclerViews. With listviews I used the common technique described here to store and restore scroll position between activities.

Create an index on a huge MySQL production table without table locking

mysql indexing production alter-table table-locking

I need to create an index on a ~5M rows MySQL table. It is a production table, and I fear a complete block of everything if I run a CREATE INDEX statement...