CODEWITHSUNDEEP
mongodbpymongo
Cristian
Cristian

Reputation: 495

How to Update field with result of $multiply MongoDB

I have a collection of documents that look like this:

{
portfolio: [
    0: {
        price: 27
        amount: 10
        balance: 0
    }
    1: {
        price: 28
        amount: 30
        balance: 0
    }
    2: {
        price: 39
        amount: 20
        balance: 0
    }
]
}

I'm trying to update the value of balance for all elements in the array with the multiple of price and amount

This is the line of code I'm trying to execute:

collection.update({}, [{"$set": {"portfolio.$[].balance": {"$multiply": ["portfolio.$.price","portfolio.$.amount"]}}}])

I keep getting the error:

builtins.TypeError: document must be an instance of dict, bson.son.SON, or any other type that inherits from collections.Mapping

What am I doing wrong?

PS: I'm using [] brackets around {$set} of let update() know it's an aggregation pipeline. I'm using $[] to update all values for all elements of portfolio

UPDATE:

Can complete the task with logic spelled out in python for anyone interested:

accounts = collection.find()
for account in accounts:
    portfolio = account["portfolio"]
    accountid = ObjectId(account["_id"])
            for i, asset in enumerate(portfolio):
                asset["balance"] = asset["price"] * asset["amount"]
                portfolio[i] = asset

   collection.update({"_id": accountid}, {"$set": {"portfolio": portfolio}})

If there is a way to do this in MongoDB, please let me know.

Upvotes: 1

Views: 419

Answers (1)

hhharsha36
hhharsha36

Reputation: 3349

I think the below query is what you are looking for.

Note: This query will only work on MongoDB version >= 4.2

You can make use of the $map operator in a $projection pipeline stage and finally the $set pipeline to update the result obtained. All this is achieved since MongoDB >= 4.2 supports Aggregation Pipeline stages within its update parameter.

db.tmp1.update({},
[
  {
    "$project": {
      "portfolio": {
        "$map": {
          "input": "$portfolio",
          "as": "pf",
          "in": {
            "balance": {
              "$multiply": [
                "$$pf.price",
                "$$pf.amount"
              ]
            },
            "price": "$$pf.price",
            "amount": "$$pf.amount",
            
          }
        }
      }
    }
  },
  {
    "$set": {
      "portfolio": "$portfolio",
      
    }
  },
], { multi: true })

$[] will work only when you want to update a single static value to all the array elements.

Upvotes: 1

Related Questions