Search code examples
pythondjangopostgresqldjango-ormdjango-3.0

Aggrgate JSON key value of JSONField Django PostgreSQL


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.

1.

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

2.

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.

Question

What is the proper/best way to get the sum of top-level JSON keys of a JSONField?


Versions

  • Python 3.8
  • Django 3.1.X

Solution

  • 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.