How do I query the length of a Django ArrayField?

Question

I have an ArrayField in a model, I'm trying to annotate the length of this field ( so far without any luck)

F('field_name__len') won't work since join is not allowed inside F(). Even ModelName.objets.values('field_name__len') is not working

Any idea?

I'm using django 1.11

Answer 1

The extra() function has been deprecated according to the docs:

Use this method as a last resort

This is an old API that we aim to deprecate at some point in the future. Use it only if you cannot express your query using other queryset methods.

Here is how you can do the same thing using a custom Annotation function:

from django.db import models

class ArrayLength(models.Func):
    function = 'CARDINALITY'

MyModel.objects.all().annotate(field_len=ArrayLength('field')).order_by('field_len')

Note that the cardinality() function is available in PostgreSQL 9.4 or later. If you're using an older version, you have to use array_length():

MyModel.objects.all().annotate(field_len=Func(F('field'), 1, function='array_length')).order_by('field_len')

One caveat with this second query is that an empty array will be sorted in front of all non-empty ones. This could be solved by coalescing NULL values from array_length to 0.