How to sum the data from a JSONField in Django?

Question

I have a JSONField in my Order model.

products = models.JSONField()

I need to extract some products from orders with the same SKU number.

orders=Order.objects.filter(products__contains=[{"sku":"002-2-1"}])

for e in orders:
    print(e.products)

>>> [{'sku': 002-2-1, 'price': '366.00'}, {'sku': 002-2-1, 'price': '366.00'}] # 2 products in 1 order
>>> [{'sku': 002-2-1, 'price': '366.00'}]  # 1 product in the order

How can I Sum the prices? I tried

orders.aggregate(earned_sum=Func(Sum(F("products__contains=[{"price"}]")))

but it don't works.

Answer 1

This is likely one of the many reasons not to use JSON fields in a relational database: it makes queries more complicated.

Normally you make two models Product and `Order with a many-to-many field in between:

class Product(models.Model):
    sku = models.CharField(max_length=16)
    price = models.DecimalField(max_digits=12, decimal_places=2)


class Order(models.Model):
    products = models.ManyToManyField(
        Product, related_name='orders', through='ProductOrder'
    )


class ProductOrder(models.Model):
    product = models.ForeignKey(Product, on_delete=models.CASCADE)
    order = models.ForeignKey(Order, on_delete=models.CASCADE)
    quantity = models.IntegerField(default=1)

we can then determine the price of an Orders object with, with my_orders a QuerySet of Order objects:

from django.db.models import F, Sum

my_orders.aggregate(
    total=Sum(F('productorder__quantity') * F('products__price'))
)['total']

or for a specific sku:

from django.db.models import F, Sum

my_orders.filter(products__sku='002-2-1').aggregate(
    total=Sum(F('productorder__quantity') * F('products__price'))
)['total']