[Django]-Django JSONField filtering Queryset where filter value is annotated sum value

by

4👍

✅

        queryset = Animal.objects.annotate(
            json=Cast(F('data'), JSONField()),
            sold_count_sum = Sum('sold__count'),
            sold_times = Count('sold'),
        ).filter(
            Q(sold_times=0) | Q(sold_count_sum__lt=Cast(
                 KeyTextTransform('count', 'json'), IntegerField())
            ),
            # keyword filtering here ...
            # client = client
        )

this is what works for me, but it can be optimized with a good JSONF field probably

we can also (re)move the json annotation and use casted version of data (may have some performance improvement):

        queryset = Animal.objects.annotate(
            sold_count_sum = Sum('sold__count'),
            sold_times = Count('sold'),
        ).filter(
            Q(sold_times=0) | Q(sold_count_sum__lt=Cast(
                 KeyTextTransform('count', Cast(
                     F('data'), JSONField())), IntegerField()
                 )
            ),
            # keyword filtering here ...
            # client = client
        )
👤Искрен Станиславов

1👍

The F class doesn’t support a JSONField at this time, but you might try making your own custom expression as described in the related ticket.

👤Bernhard Vallant

1👍

How about something like this:

from django.db.models import Sum, F
from django.contrib.postgres.fields.jsonb import KeyTransform

Animal.objects.annotate(animals_sold=Sum('sales_set__count'), data_count=KeyTransform('count', 'data')).filter(data_count__gt=F('animals_sold'))
👤jdeanwallace

Leave a comment