Sum aggregation over a JsonField using Django Orm

Question

I have a model in my django project with the following structure:

class Portfolio(models.Model):
    user = models.OneToOneField(User, on_delete=models.CASCADE)
    entered_usdt = models.DecimalField(max_digits=22, decimal_places=2, default=0)
    detail = models.JSONField(null=True, blank=True)
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)

I would like to have a Sum aggregate function over non-zero value keys of detail field of records this model in database.

This field may be empty or have different keys for earch record in database for example I capture a section of database to better explain what I mean:

Some records screenshot

For this purpose, I wrote the below code snippet with pure Python, which first extracts their keys for records whose details are not empty, and then calculates the sum of all those keys in all those records.

import json
from bestbuy.models import Portfolio
from django.db.models import Q

def calculate_sum_of_all_basket_coins():
    non_zero_portfolios_details = list(Portfolio.objects.filter(~Q(detail={})).values('detail'))

    detail_list = list()
    result = {}

    for portfolio_detail in non_zero_portfolios_details:
        detail_list.append(json.loads(portfolio_detail))


    for d in detail_list:
        for k in d.keys():
            if k in result: 
                result[k] += d.get(k, 0.0)
            else:
                result[k] = d.get(k, 0.0)
    return result

I know this method is not good at all because as the database records increase, this function takes more and more time to perform this calculation.

I also read the below blog posts and issues to find out something more about django orm and the possibility of it to support this calculation.

For example I found out with something like KeyTextTransform function, but all of the tricks in below links is for a specific key or keys of a jsonfield in database not for some unknown keys etc.

How can I have a queryset of the aggregated values of this field using django orm?

Why not just create a manager method that returns the sum of each of the fields? — pygeek, Jul 26 '22 at 01:27

pygeek · Accepted Answer · 2022-07-26T12:42:23.997

Best practice here would be to create a Model Manager, this way it can be called like so:

Projects.objects.with_sums()

It can even be chained like so:

Projects.objects.filter(…).with_sums()

Just annotate the query object being returned by the model method with the sum of each field—you will need to query for the sum of each field individually, however.

If you wanted to optimize things a bit, you could allow with_sums() to take an argument where you can specify which fields you would like sums for—I recommend passing the full path of the json field (ie: “foo__bar__field”).

References

https://docs.djangoproject.com/en/4.0/topics/db/managers/

https://docs.djangoproject.com/en/4.0/topics/db/aggregation/

https://docs.djangoproject.com/en/4.0/ref/models/querysets/#annotate

Sum aggregation over a JsonField using Django Orm

1 Answers1

References