1

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?

Irina_Xena
  • 245
  • 1
  • 11
  • Could you please add the `Order` model to displayed code? I'd like to see how you defined the `Order.products`. – Tarquinius Jul 20 '23 at 10:41
  • @ Tarquinius, products in the Order model ```products = models.JSONField()``` – Irina_Xena Jul 20 '23 at 10:45
  • I found a ```mean(Decimal(d['price']) for d in sum(list(orders.values_list("products", flat=True)), [])) ```, but I think it's too complicated. – Irina_Xena Jul 20 '23 at 10:50

1 Answers1

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!

Tarquinius
  • 1,468
  • 1
  • 3
  • 18
  • thank you, but it don't work, because "product' is a list [{'sku': 002-2-1, 'price': '400.00'}]. So, each products field is a list with one dict. The method that works, I wrote above, but I don't like it. I can try ```queryset.aggregate(Avg("products__0__price"))``` but I get an error ```TypeError: the JSON object must be str, bytes or bytearray, not float```. I agree that we need to get rid of JSONField. But I can't do that right now. – Irina_Xena Jul 20 '23 at 14:37
  • Ok, I see. Maybe [this](https://stackoverflow.com/q/34325096/18018869) is helpful for you? Instead of min/max you would want to go for `Avg` – Tarquinius Jul 20 '23 at 15:21
  • Hey, Irina_Xena. I see you accepted the answer. Do I need to edit my answer to make it 100% correct? I am asking because you at first said it is not working out for you. – Tarquinius Jul 25 '23 at 13:12
  • anyway, your solution works if the structure of products was a dict. – Irina_Xena Jul 26 '23 at 11:51