22

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

  1. ['dog', 'cat']
  2. ['dog']
  3. ['cat']

would yield ['dog', 'cat']

I know how to do that in Python but would like to find out a way to aggregate this on the DB level. Using Django 1.8.4

Ivan
  • 5,803
  • 2
  • 29
  • 46
elynch
  • 2,150
  • 1
  • 18
  • 20

1 Answers1

46

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)
    # ...

the Django equivalent is:

from django.db.models import Func, F
TheModel.objects.annotate(arr_els=Func(F('array_field'), function='unnest'))\
                .values_list('arr_els', flat=True).distinct()
D Malan
  • 10,272
  • 3
  • 25
  • 50
Ivan
  • 5,803
  • 2
  • 29
  • 46
  • I'm trying to filter on existence in any children field, this annotation doesn't seem to have the right type in Django 1.9 if I `.filter(id__not_in=F('arr_els'))` I get `Unsupported lookup 'not_in' for AutoField or join on the field not permitted.` – Will S Oct 09 '20 at 12:30
  • @WillS there isn't `not_in` lookup keyword, try to `exclude` them instead of `filter` (if it's still relevant) – Ersain Apr 25 '22 at 10:27