[Answered ]-Aggrgate JSON key value of JSONField Django PostgreSQL

by

1👍

✅

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.

👤Bilal Tonga

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}
👤JPG

Leave a comment