Django JSONField filtering Queryset where filter value is annotated sum value

Question

How do I properly write the filter code so it returns only the Animals that are not sold out.

I'm using POSTGRES db, python3.6 and Django 2.1.7 (currently there are v2.2a1,v2.2b1 pre-release versions)

My questioin is an extension to Django JSONField filtering which filters on a hard coded value in the filter.

My Case requires an annotated value in the filter.

models.py I know that the models can be optimized, but I already have huge amount of records since more than 3 years

from django.db import models
from django.contrib.postgres.fields import JSONField

class Animal(models.Model):
    data = models.JSONField(verbose_name=_('data'), blank=True)

class Sell(models.Model):
    count = models.IntegerField(verbose_name=_('data'), blank=True)
    animal = models.ForeignKey('Animal', 
                               on_delete=models.CASCADE,
                               related_name="sales_set",
                               related_query_name="sold"
   )

in my api I want to return only the animals that still have something left for selling

animal = Animal(data={'type':'dog', 'bread':'Husky', 'count':20})

What I want to filter should be similar to animal.data['count'] > sum(animal.sales_set__count

Animal.objects.annotate(animals_sold=Sum('sales_set__count'))
.filter(data__contains=[{'count__gt': F('animals_sold')}])

with the code above i get builtins.TypeError TypeError: Object of type 'F' is not JSON serializable

if I remove the F it won't filter on the value of the animals_sold, but on the text 'animals_sold' and it doesn't do any help.

Animal.objects.annotate(animals_sold=Sum('sales_set__count'))
.filter(data__contains=[{'count__gt': F('animals_sold')}])

Edit 1: There is one more topic here that can be linked: Postgres: values query on json key with django

Edit 2: here is some additional code with custom transform classes as suggested in related django ticket

from django.db.models.constants import LOOKUP_SEP
from django.db.models import F, Q, Prefetch, Sum
from django.db.models import IntegerField, FloatField, ExpressionWrapper
from django.db.models.functions import Cast
from django.contrib.postgres.fields import JSONField
from django.contrib.postgres.fields.jsonb import KeyTransform, KeyTextTransform

class KeyIntegerTransform(KeyTransform):  # similar to KeyTextTransform
    """ trasnform the data.count to integer """
    operator = '->>'
    nested_operator = '#>>'
    output_field = IntegerField()

class KeyIntTransformFactory:
    """ helper class for the JSONF() """

    def __init__(self, key_name):
        self.key_name = key_name

    def __call__(self, *args, **kwargs):
        return KeyIntegerTransform(self.key_name, *args, **kwargs)


class JSONF(F):
    """ for filtering on JSON Fields """

    def resolve_expression(self, query=None, allow_joins=True, reuse=None, summarize=False, for_save=False):
        rhs = super().resolve_expression(query, allow_joins, reuse, summarize, for_save)
        field_list = self.name.split(LOOKUP_SEP)
        for name in field_list[1:]:
            rhs = KeyIntegerTransform(name)(rhs)
        return rhs

queryset filtering I tried so far:

q = q.filter(data__contains={'count__gt':JSONF('sold_count_sum')})
# err: Object of type 'JSONF' is not JSON serializable

q = q.filter(sold_count_sum__lt=Cast(JSONF('data_count'), IntegerField()))
# err: operator does not exist: text ->> unknown

q = q.filter(sold_count_sum__lt=Cast(JSONF('data__count'), IntegerField()))
# err: 'KeyIntegerTransform' takes exactly 1 argument (0 given)

q = q.filter(sold_count_sum__lt=KeyIntegerTransform('count', 'data'))
# err: operator does not exist: text ->> unknown

q = q.filter(sold_count_sum__lt=F('data__count'))
# err: operator does not exist: text ->> unknown

q = q.filter(sold_count_sum__lt=F('data_count'))
# err: operator does not exist: text ->> unknown

q = q.filter(sold_count_sum__lt=JSONF('data_count'))
# err: operator does not exist: text ->> unknown

q = q.filter(sold_count_sum__lt=JSONF('data__count'))
# err: 'KeyIntegerTransform' takes exactly 1 argument (0 given)

q = q.filter(sold_count_sum__lt=JSONF('data', 'count'))
# err: JSONF.__init__() takes 2 params

Искрен Станиславов · Accepted Answer · 2019-02-22T08:16:20.177

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
        )

edited Feb 22 '19 at 08:16

answered Feb 21 '19 at 10:36

Искрен Станиславов

527
5
23

Yes I also briefly played around with it and came up with nearly the same, it's not a bad solution you just always need to cast the values from the JSON. Most likely you don't need to annotate the `json` if you use `KeyTextTransform('count', 'data')`. – Bernhard Vallant Feb 21 '19 at 11:05
I get `unhashable type: 'list' ` if I use KeyTextTransform('count', 'data') instead of 'json' as in `sold_count_sum__lt=Cast(KeyTextTransform('count', KeyTextTransform('count', 'data')), IntegerField())`, but using `Cast(KeyTextTransform('count', Cast(F('data'), JSONField())), IntegerField())` works fine – Искрен Станиславов Feb 22 '19 at 08:02
I am not sure if this can be useful for the ticket you mentioned in here. – Искрен Станиславов Feb 22 '19 at 08:03

score 1 · Answer 2 · edited Feb 20 '19 at 13:08

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.

edited Feb 20 '19 at 13:08

Искрен Станиславов

527
5
23

answered Feb 19 '19 at 14:02

Bernhard Vallant

49,468
20
120
148

Do I get you right? Do you mean that I can only use pure SQL query? – Искрен Станиславов Feb 19 '19 at 17:02
1

No you can also create your own custom expression as explained in the alst comment in the ticket https://code.djangoproject.com/ticket/29769#comment:5 – Bernhard Vallant Feb 20 '19 at 10:42
I am not deep enough into how this part of django is functioning, so I won't manage to build my own JSONF function. I am considering refactoring of the models and transitioning the data from the data field into model fields. btw, what is the value of the const `LOOKUP_SEP`? in the comment there? – Искрен Станиславов Feb 20 '19 at 12:22
1

https://github.com/django/django/blob/b9cf764be62e77b4777b3a75ec256f6209a57671/django/db/models/constants.py#L6 – Bernhard Vallant Feb 20 '19 at 13:07
I have added more code in my question. it seems that a small change in the JSONF should do the trick, can you please check what is wrong? – Искрен Станиславов Feb 20 '19 at 16:00

score 1 · Answer 3 · answered Feb 19 '19 at 14:37

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'))

answered Feb 19 '19 at 14:37

jdeanwallace

1,146
9
13

I have already tried that and I just tried it again, but I get `ProgrammingError`: ```operator does not exist: jsonb > bigint LINE 1: ...unt') HAVING ("my_secret_app_animal"."data" -> 'count') > (SUM("fi...``` – Искрен Станиславов Feb 19 '19 at 16:25
It is almost the same if the data.count is float. can you suggest a working `Cast`? – Искрен Станиславов Feb 19 '19 at 16:30

Django JSONField filtering Queryset where filter value is annotated sum value

3 Answers3

Linked