Django Postgresql ArrayField Aggregation

Question

In my Django application using Postgresql, I have a model with ArrayField from CharFields. I would like to know if there is a DB way to aggregate and get a list of all the rows in a table. For example:

• ['dog', 'cat']
• ['dog']
• ['cat']

will give ['dog', 'cat']

I know how to do this in Python, but would like to know a way to aggregate this at the DB level. Using Django 1.8.4

Answer 1

In PostgreSQL, you can do the following:

SELECT DISTINCT UNNEST(array_column) FROM the_table;

So, if your model looks something like

class TheModel(models.Model):
    # ...
    array_field = ArrayField(models.CharField(max_length=255, blank=True),\
                             default=list)
    # ...

Django equivalent:

TheModel.objects.annotate(arr_els=Func(F('array_field'), function='unnest'))\
                .values_list('arr_els', flat=True).distinct()