I have a simple model setup as below,
import random
import string
from django.db import models
def random_default():
random_str = "".join(random.choice(string.ascii_uppercase + string.digits) for _ in range(10))
return {"random": random_str, "total_price": random.randint(1, 100)}
class Foo(models.Model):
cart = models.JSONField(default=random_default)
I want to get the sum of total_price
from all Foo
instances. In native Python, I can do something like below to get the sum, but I believe it is suboptimal.
sum(foo.cart["total_price"] for foo in Foo.objects.all())
I tried the following aggregate queries with Django, but none seems correct/working.
Foo.objects.aggregate(total=models.Sum(Cast('cart__total_price', output_field=models.IntegerField())))
# Error
# django.db.utils.DataError: cannot cast jsonb object to type integer
Foo.objects.aggregate(total=models.Sum('cart__total_price', output_field=models.IntegerField()))
# Error
# django.db.utils.ProgrammingError: function sum(jsonb) does not exist
# LINE 1: SELECT SUM("core_foo"."cart") AS "total" FROM "core_foo"
^
# HINT: No function matches the given name and argument types. You might need to add explicit type casts.
What is the proper/best way to get the sum of top-level JSON keys of a JSONField?
You can use KeyTextTransform and aggregate function together to achieve this.
from django.db.models import Sum, IntegerField
from django.db.models.fields.json import KeyTextTransform
from django.db.models.functions import Cast
# Sum of total_price from all Foo instances
total_price_sum = Foo.objects.annotate(
total_price_int=KeyTextTransform('total_price', 'cart')
).aggregate(
total=Sum(Cast('total_price_int', output_field=IntegerField()))
)
print(total_price_sum['total'])
Uses KeyTextTransform to extract the value of the 'total_price' key from the 'cart' JSON field.
Then, aggregate the sum of the extracted values using the Sum function and the Cast function to convert the values to integers.