Question 137905 in PostgreSQL Question posted in
The official documentation can be found here.

Aggregate (nested) 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
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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}

  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search