Django Arrayfiled: Annotate queryset with the first item in the arryafield

Question

I have a model that can be represented by something like this.

class BackPack(models.Model):
    person = models.ForeignKey(Person, db_index=True,
                             related_name='back_packs', on_delete=models.CASCADE)
    candy = ArrayField(models.CharField(max_length=203, null=True))

I want to build a queryset that is all back packs associated with one person and then annotated with the first item in the candy arrayfield. I tried the following;

first_candy = BackPack.objects.filter(person__id=200)\
                              .annotate(first_candy=F('candy__0'))

first_candy = BackPack.objects.filter(person__id=200)\
                              .annotate(first_candy=ExpressionWrapper(F('candy__0'),
                                                 output_field=CharField()))

The output for first_candy includes every item in the arrayfield not just the first one.

Any help for the correct way of doing this is much appreciated.

score 2 · Accepted Answer · answered Feb 27 '20 at 20:57

2

Try this:

from django.db.models.expressions import RawSQL

BackPack.objects.filter(person__id=200).annotate(first_candy=RawSQL('candy[1]', ()))

Postgres arrays are 1-based by default

answered Feb 27 '20 at 20:57

Ulladimil

36
4

Legendary! I have been making a really convoluted workaround since I asked the question. You're a star! – davenenglish Feb 29 '20 at 20:40
Also it needs some minor advancement for annotated ArrayField: `.annotate(field_arr=(...output=ArrayField(CharField())).annotate(first_el=RawSQL("'field_arr[1]'", ()))` – Damir Nafikov Aug 22 '23 at 08:30

Django Arrayfiled: Annotate queryset with the first item in the arryafield

1 Answers1