I have a MongoDB collection "orders" with document similar to such:
{
"_id": ObjectId("xxxxx"),
"orderNo": 0,
"products": [
{ "product1": ["_Id": objectId("eeee"),"productName", "quantity", "price"] },
{ "product2": ["_Id": objectId("ffff"),"productName", "quantity", "price"] }
],
"deliveryAddress": "addsfdfghf"
}
{
"_id": ObjectId("yyyyy"),
"orderNo": 1,
"products": [
{ "product1": ["_Id": objectId("dddd"),"productName", "quantity", "price"] }
],
"deliveryAddress": "rwetrutytyi"
}
{
"_id": ObjectId("zzzz"),
"orderNo": 2,
"products": [
{ "product1": ["_Id": objectId("aaaa"), "productName", "quantity", "price"] },
{ "product2": ["_Id": objectId("bbbb"),"productName", "quantity", "price"] },
{ "product3": ["_Id": objectId("cccc"),"productName", "quantity", "price"] }
],
"deliveryAddress": "rwetrutytyi"
}
Now I want to write a query to get top selling products using "quantity" property of every products.
correct values of in above document look like:
{
"products": [{
"_id": {
"$oid": "612baf1b89c77b25c40aa3bc"
},
"productID": "NPZ6I",
"productName": "Cosentys 150ml",
"quantity": "1",
"expireDate": "04/20/2022",
"price": "22200"
}, {
"_id": {
"$oid": "612baf1b89c77b25c40aa3bb"
},
"productID": "NPZ3I",
"productName": "Asunra 400mg",
"quantity": "2",
"expireDate": "09/26/2021",
"price": "13200"
}],
"orderNo": 9,
"deliveryAddress": "Dhaka"
}
I want to return an object look like:
{ "_id": ObjectId("yyyyy"), "productName": "String", "productID": "String", "totalSales" : "Number(sum of each products quantity)", "totalEarning": "Number(sum of each product's price)"
}