Django Postgres ArrayField aggregation and filtering

Question

Following on from this question: Django Postgresql ArrayField aggregation

I have an ArrayField of Categories and I would like to retrieve all unique values it has - however the results should be filtered so that only values starting with the supplied string are returned.

What's the "most Django" way of doing this?

Given an Animal model that looks like this:

class Animal(models.Model):
    # ...
    categories = ArrayField(
        models.CharField(max_length=255, blank=True),
        default=list,
    )
    # ...

Then, as per the other question's answer, this works for finding all categories, unfiltered.

all_categories = (
    Animal.objects
    .annotate(categories_element=Func(F('categories'), function='unnest'))
    .values_list('categories_element', flat=True)
    .distinct()
)

However, now, when I attempt to filter the result I get failure, not just with __startswith but all types of filter:

all_categories.filter(categories_element__startswith('ga'))
all_categories.filter(categories_element='dog')

Bottom of stacktrace is:

DataError: malformed array literal: "dog"
...
DETAIL:  Array value must start with "{" or dimension information.

... and it appears that it's because Django tries to do a second UNNEST - this is the SQL it generates:

...) WHERE unnest("animal"."categories") = dog::text[]

If I write the query in PSQL then it appears to require a subquery as a result of the UNNEST:

SELECT categories_element
FROM (
    SELECT UNNEST(animal.categories) as categories_element
) ul
WHERE ul.categories_element like 'Ga%';

Is there a way to get Django ORM to make a working query? Or should I just give up on the ORM and use raw SQL?

can maybe use docs.djangoproject.com/en/1.9/ref/models/querysets/#extra to get the subquery in there. annotate seems kind of wrong (it's usually for aggregation type functions) — Anentropic
– Anentropic, Commented Apr 20, 2016 at 17:13
Thanks @Anentropic - I did have a look at extra but couldn't see a way to add the subquery. The answer below is probably the "truest" - the schema is not fit for purpose really. I've implemented a mini query builder that selects and sorts from the ArrayFields and appears to be working nicely. — jamesc
– jamesc, Commented Apr 21, 2016 at 10:45
I wondered if you could put the subquery in tables parameter — Anentropic
– Anentropic, Commented Apr 21, 2016 at 12:17

e4c5 · Accepted Answer · 2016-04-20 11:47:24Z

2

You probably have the wrong database design.

Tip: Arrays are not sets; searching for specific array elements can be a sign of database misdesign. Consider using a separate table with a row for each item that would be an array element. This will be easier to search, and is likely to scale better for a large number of elements.

http://www.postgresql.org/docs/9.1/static/arrays.html

answered Apr 20, 2016 at 11:47

e4c5

53.9k11 gold badges110 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

jamesc Over a year ago

Yes I completely agree that the design is not optimal. But it's not my design and I'm stuck with it for now :( Poor Animals!

e4c5 Over a year ago

In that case there is no reason why shouldn't use a raw query. Raw queries are not evil.

jamesc Over a year ago

Thanks - I'll give it a go!

Collectives™ on Stack Overflow

Django Postgres ArrayField aggregation and filtering

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related