CODEWITHSUNDEEP
djangodjango-3.0django-postgresqldjango-aggregationdjango-jsonfield
Bimal Prasad Pandey
Bimal Prasad Pandey

Reputation: 581

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

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

Upvotes: 7

Views: 2993

Answers (2)

thenav56
thenav56

Reputation: 21

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']

Upvotes: 2

allexiusw
allexiusw

Reputation: 1743

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])

Upvotes: 1

Related Questions