3

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]}]}
         }
       }
    ])
ray
  • 11,310
  • 7
  • 18
  • 42
Ven Nilson
  • 969
  • 4
  • 16
  • 42

1 Answers1

1

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.

ray
  • 11,310
  • 7
  • 18
  • 42
  • Thank you, for answering this question. Can you please explain this query to me I mean what is `as` and double dollar sign? – Ven Nilson Dec 23 '21 at 15:30
  • 1
    @VenNilson `$map` is basically iterating the array and transforming the elements one-by-one. `$$s` is the variable I assigned for the individual array element, as specified in `"as": "s"` clause. For the `in` clause, I specified the transformation/output, which contains both the original fields and the `percentageSold` field. You can read more about `$map` in [this official document](https://docs.mongodb.com/manual/reference/operator/aggregation/map/) – ray Dec 23 '21 at 15:34