Suppose I have data in bookOrder as:

{
    "_id" : ObjectId("615fc295257d6d7cf57a39fe"),
    "orderId" : "2001",
    "itemId" : [ 
        "615fc232257d6d7cf57a39d4", 
        "615fc251257d6d7cf57a39e0"
    ],
    "Discount" : 10
}

Item data as:

{
    "_id" : ObjectId("615fc232257d6d7cf57a39d4"),
    "itemId" : "1001",
    "Price" : 10.21
} 

{
    "_id" : ObjectId("615fc251257d6d7cf57a39e0"),
    "itemId" : "1002",
    "Price" : 100
}

I want to calculate the total price of order after discount,

i.e. total price as : 100 10.21-10 = 100.21

For this I tried as:

const data = await db.order.aggregate(
        [
            {
                "$match": {
                    "orderId": orderId
                }
            },
            {
                "$lookup": {
                    "from": "item",
                    let: {
                        eid: "$itemId"
                    },
                    pipeline: [
                        {
                            "$match": {
                                $expr: {
                                    $in: [
                                        "$_id",
                                        "$$eid"
                                    ]
                                }
                            }
                        },
                    ],
                    "as": "items"
                }
            },
            {
                "$unwind": {
                    path: "$items"
                }
            },
        ]
    )

So, I get the value as:

{
    "orderId" : "2001",
    "Discount":10,
    "itemId":[{
        "itemId" : "1001",
        "Price" : 10.21
    },
        "itemId" : "1002",
        "Price" : 100
    ]}
}

SO instead of having to loop over the itemId price and get total sun, and then subtracting from the discount price of order can we do all these calculations of db itself.

Is there any way that I can query the total price from DB only instead of having to fetch data and applying any loop and then calculating the total price?

Please let me know if anyone needs any further explanation from my side.

CodePudding user response：

You can do this in a couple of ways, here is the most straight forward one using $map and some math operators.

db.order.aggregate([
  {
    "$match": {
      "orderId": "2001"
    }
  },
  {
    "$lookup": {
      "from": "item",
      let: {
        eid: "$itemId"
      },
      pipeline: [
        {
          "$match": {
            $expr: {
              $in: [
                "$_id",
                "$$eid"
              ]
            }
          }
        },
        
      ],
      "as": "items"
    }
  },
  {
    $project: {
      orderId: 1,
      finalSum: {
        $subtract: [
          {
            $sum: {
              $map: {
                input: "$items",
                in: "$$this.Price"
              }
            }
          },
          "$Discount"
        ]
      }
    }
  }
])

Mongo Playground

CodePudding user response：

use sum

aggregate

db.orders.aggregate([
  {
    "$match": {
      "orderId": "2001"
    }
  },
  {
    "$lookup": {
      "from": "items",
      "localField": "itemId",
      "foreignField": "_id",
      "as": "items"
    }
  },
  {
    "$project": {
      "orderId": 1,
      "total": {
        $subtract: [
          {
            "$sum": "$items.Price"
          },
          "$Discount"
        ]
      }
    }
  }
])

mongoplayground