1

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)"

}

0 Answers0