2

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.

Sushant Rad
  • 249
  • 2
  • 10
  • Obviously will need explanation Your question has little less explanation. Maybe little more of it? Also you can put any error message you have got if any. – itiDi Oct 08 '21 at 04:32
  • hahah.... yes I have edited the question with O/P that I am getting and my expectation, hopefully it makes sense now. – Sushant Rad Oct 08 '21 at 04:55

2 Answers2

1

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

Tom Slabbaert
  • 21,288
  • 10
  • 30
  • 43
1

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

YuTing
  • 6,555
  • 2
  • 6
  • 16