MongoDb operator not working for array of object properties-CodePudding

I am facing a problem with MongoDB query. Our collection named is products and the data is placed something like this.

    {
      "_id":"61b823681975ba537915cb0c",
      "salesInfo":{
        "_id":"61b823681975ba537915c23c",
        "salesDate":[
          {
            "_id":"61b3aa4a7b04b30cd0a76b06",
            "salesQuantity":100,
            "soldPieces":36,
            "holdPieces":0
          },
          {
            "_id":"61b3aa4a7b04b30cd0a75506",
            "salesQuantity":100,
            "soldPieces":36,
            "holdPieces":0
          }
        ]
      }
    }

I want to add a new field named percentageSold inside an array of objects, and the value should be the calculation of the following formula ((soldPieces holdPieces) / salesQuantity * 100).

My query is this but it is returning null for the percentageSold property.

    db.products.aggregate( [
       {
         $addFields: {
           "salesInfo.salesDate.percentageSold": {$divide: [{$add: ["$salesDate.soldPieces", "$salesDate.holdPieces"]}, {$multiply: ["$salesDate.salesQuantity", 100]}]}
         }
       }
    ])

CodePudding user response：

As salesInfo.salesDate is an array field, you need to to use array operator like $map to perform element-wise operation.

db.products.aggregate([
  {
    $addFields: {
      "salesInfo.salesDate": {
        "$map": {
          "input": "$salesInfo.salesDate",
          "as": "s",
          "in": {
            "_id": "$$s._id",
            "salesQuantity": "$$s.salesQuantity",
            "soldPieces": "$$s.soldPieces",
            "holdPieces": "$$s.holdPieces",
            "percentageSold": {
              $divide: [
                {
                  $add: [
                    "$$s.soldPieces",
                    "$$s.holdPieces"
                  ]
                },
                {
                  $multiply: [
                    "$$s.salesQuantity",
                    100
                  ]
                }
              ]
            }
          }
        }
      }
    }
  }
])

Here is the Mongo playground for your reference.