How to annotate sum over Django JSONField (Array of objects) data?

Question

I have models sth like this

# models.py
class MyModel( models.Model ):
    orders = models.JsonField(null= True, blank=True, default=list)
    category = models.ForeignKey(Category, on_delete=models.CASCADE)

I stored json data in this structure.

[
    {
        "order_name": "first order",
        "price": 200
    },
    {
        "order_name": "second order",
        "price": 800
    },
    {
        "order_name": "third order",
        "price": 100
    }
]

I want to sum price of all json objects ie 200+800+100

Phira

Answer 1

One way will be to use jsonb_array_elements to break each value into rows and then use the normal aggregate function.

For eg:

from django.db import models


Model.objects.annotate(
    # This will break items into multiple rows
    annotate_field_1=models.Func(models.F('array_field__items'), function='jsonb_array_elements'),
).aggregate(
    # Then calculate the total.
    total=models.Count('annotate_field_1'),
)['total']

Simonton

Thaks @Simonton but UNNEST postgres function only work with ArrayField in my knowledge, because filtering Array of JsonField is disaster in django, I am using JsonField and storing array of json objects in JsonField. – Phira

Have you tried jsonb functions jsonb_array_elements postgresql.org/docs/9.5/functions-json.html? – Simonton

Answer 2

I haven't worked with JSONArrayField but I did a little bit of research and found that the following example can give you a clue:

MyModel.objects.annotate(
    order_price_sum=Sum(
        Cast(
            KeyTextTransform("price", "orders"), models.FloatField()
        )
    ),
)

I tried to implement it to your specific question you can find more helpfull information in the following link: https://dev.to/saschalalala/aggregation-in-django-jsonfields-4kg5

Workaround: I was trying to figure out how to manage JSONArray using annotate in django but it seems to not be well-documented so I share this workaround to achieve the goal:

total = 0
for i in MyModel.objects.exclude(orders__isnull=True).values('orders'):
    total += sum([j.get('price',0) for j in i.get('orders') if j is not None])

Aryan

Thanks @Aryan for answer, but this solution only works with single json object. I am looking for array of json objects. – Phira

Did you tried it? I so that ORM seems to work in the same way with json objects and arrays... It gave you some error o something like that? – Aryan

Above code returns <QuerySet [{'order_price_sum': None}]> – Phira

You are right, all that I have is workaround using python code to manage the task that you have, hope you share the answer if you achieve it with annotate. – Aryan