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

thenav56 · Accepted Answer · 2021-09-07 17:04:26Z

2

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

edited Sep 7, 2021 at 17:04

answered Sep 7, 2021 at 3:38

thenav56

211 silver badge3 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Bimal Prasad Pandey Over a year ago

Thaks @thenav56 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.

thenav56 Over a year ago

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

allexiusw · Accepted Answer · 2021-09-06 16:42:19Z

1

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

edited Sep 6, 2021 at 16:42

answered Sep 4, 2021 at 3:53

allexiusw

1,7822 gold badges18 silver badges27 bronze badges

4 Comments

Bimal Prasad Pandey Over a year ago

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

allexiusw Over a year ago

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?

Bimal Prasad Pandey Over a year ago

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

allexiusw Over a year ago

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.

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related