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 · Answer 1 · 2021-09-07T17:04:26.070

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 07 '21 at 17:04

answered Sep 07 '21 at 03:38

thenav56

21
1
3

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. – Bimal Prasad Pandey Sep 07 '21 at 03:51
Have you tried jsonb functions `jsonb_array_elements` https://www.postgresql.org/docs/9.5/functions-json.html? – thenav56 Sep 07 '21 at 08:39

allexiusw · Answer 2 · 2021-09-06T16:42:19.823

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 06 '21 at 16:42

answered Sep 04 '21 at 03:53

allexiusw

1,533
2
16
23

1

Thanks @allexiusw for answer, but this solution only works with single json object. I am looking for array of json objects. – Bimal Prasad Pandey Sep 05 '21 at 08:15
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? – allexiusw Sep 05 '21 at 21:36
Above code returns – Bimal Prasad Pandey Sep 06 '21 at 02:33
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. – allexiusw Sep 06 '21 at 07:50

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

2 Answers2

Linked