Aggrgate JSON key value of JSONField Django PostgreSQL

Question

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

score 1 · Accepted Answer · answered May 05 '23 at 18:45

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.

score 0 · Answer 2 · answered May 03 '23 at 08:23

I created a custom DB function for jsonb_extract_path_text(...)

from django.db.models import Aggregate


class JSONBExtractPathText(Aggregate):
    function = 'jsonb_extract_path_text'
    template = None

    def get_template(self):
        # https://stackoverflow.com/a/38985104/8283848
        paths = str(self.extra['path'])[1:-1]
        return f"%(function)s(%(jsonb_field)s, {paths})"

    def as_sql(self, *args, **kwargs):
        kwargs["template"] = self.get_template()
        return super().as_sql(*args, **kwargs)

and used as


from django.db.models.functions import Cast
from django.db.models import Sum, IntegerField
from .models import Foo

result = Foo.objects.aggregate(
    total_price=Sum(
        Cast(
            JSONBExtractPathText(
                path=["nested", "price"],
                jsonb_field="cart"),
            output_field=IntegerField(),
        )
    )
)
print(result)
# {'total_price': 100}

Aggrgate JSON key value of JSONField Django PostgreSQL

1.

2.

Question

Versions

2 Answers2

Linked