Multiply field by value in Mongodb

Question

I've been looking for a way to create an update statement that will take an existing numeric field and modify it using an expression. For example, if I have a field called Price, is it possible to do an update that sets Price to 50% off the existing value ?

So, given { Price : 19.99 }

I'd like to do db.collection.update({tag : "refurb"}, {$set {Price : Price * 0.50 }}, false, true);

Can this be done or do I have to read the value back to the client, modify, then update ? I guess the question then is can expressions be used in update, and can they reference the document being updated.

Answer 1

You can run server-side code with db.eval().

db.eval(function() {      db.collection.find({tag : "refurb"}).forEach(function(e) {         e.Price = e.Price * 0.5;         db.collection.save(e);     }); }); 

Note this will block the DB, so it's better to do find-update operation pair.

See https://docs.mongodb.com/manual/core/server-side-javascript/

Answer 2

In the new Mongo 2.6.x there is a $mul operator. It would multiply the value of the field by the number with the following syntax.

{   $mul: { field: <number> } } 

So in your case you will need to do the following:

db.collection.update(   { tag : "refurb"},   { $mul: { Price : 0.5 } } ); 

Answer 3

Starting Mongo 4.2, db.collection.update() can accept an aggregation pipeline, finally allowing the update of a field based on another field:

// { price: 19.99 }
// { price: 2.04  }
db.collection.update(
  {},
  [{ $set: { price: { $multiply: [ 0.5, "$price" ] } } }],
  { multi: true }
)
// { price: 9.995 }
// { price: 1.02  }

• The first part {} is the match query, filtering which documents to update (all documents in this case).

• The second part [{ $set: { price: ... } }] is the update aggregation pipeline (note the squared brackets signifying the use of an aggregation pipeline). $set is a new aggregation operator and an alias of $addFields. Note how price is modified directly based on the its own value ($price).

• Don't forget { multi: true }, otherwise only the first matching document will be updated.