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:
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
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()
.filter(id__not_in=F('arr_els')) I get Unsupported lookup 'not_in' for AutoField or join on the field not permitted.
not_in lookup keyword, try to exclude them instead of filter (if it's still relevant)