[Django]-How do I query the length of a Django ArrayField?

by

29👍

✅

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.

👤mart1n

6👍

ModelName.objects.extra(select={'length':'cardinality(field_name)'}).order_by('length')

you can try this

👤Exprator

6👍

Tested on django 3.5, your query now works!

I’m not sure which version added this offhand, but give it a try, or try upgrading if you can.

ModelName.objects.values('field_name__len')

<ModelName [{'field_name__len': 1}]>

Also some other things to try:

ModelName.objects.filter(field_name__len__gt=0)
ModelName.objects.filter(field_name__len__gte=0)
ModelName.objects.filter(field_name__len=0)
ModelName.objects.filter(field_name__len__lte=10)
ModelName.objects.filter(field_name__len__lt=10)
👤jmunsch

Leave a comment