MongoDb operator not working for array of object properties

Question

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 · Accepted Answer · 2021-12-23 15:28:08Z

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.

answered Dec 23, 2021 at 15:28

ray

15.6k12 gold badges96 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ven Nilson Over a year ago

Thank you, for answering this question. Can you please explain this query to me I mean what is as and double dollar sign?

ray Over a year ago

@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

Collectives™ on Stack Overflow

MongoDb operator not working for array of object properties

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related