Get mean value for price in dict (Django JSONField)

Question

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': '300.00'}] # 2 products in 1 order
>>> [{'sku': 002-2-1, 'price': '400.00'}]  # 1 product in the order

I need to find the mean value of "price"

I tried to get a one list with dicts:

a = sum(list(orders.values_list("products", flat=True)), [])

[{'sku': 002-2-1, 'price': '366.00'}, {'sku': 002-2-1, 'price': '300.00'}, {'sku': 002-2-1, 'price': '400.00'}]

How can I find the mean value of price?

[mean(e.get("price")) for e in a]

May be there is a better way to find it via F?

Answer 1

I did not try it but please give this a try:

from django.db.models import Avg

order_qs = Order.objects.filter(products__contains=[{"sku":"002-2-1"}])
print(order_qs.aggregate(Avg("products__price")))

Interesting resources:
Key, index, and path transforms - how to "deal" with jsonfield lookups
Avg - the function you want to aggregate your queryset with to get the mean value
Conditional Aggregating - general "how-to" how to aggregate with functions like Count or mentioned Avg

Even though you did not ask for a potentially better way I want to say: "Why are you using a JSONField?". In my eyes creating another model called Product and link it via a ForeignKey to Order makes much more sense. It also enables all the cool django features like aggregating, annotating and so much more. Especially if the scheme of the JSONField is always the same... what's the point in not creating a further model?

I can guarantee you that with a dedicated Product model the provided solution is going to work. With a JSONField I am not 100% sure - let me know!