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.

Please note that in an upcoming version of mongo, you can do this without all this eval and DB locking almost at no cost. Check and may be accept the new answer, so that new devs would not use obsolete info. — Salvador Dali, Nov 06 '13 at 23:31

score 37 · Accepted Answer · edited Aug 29 '18 at 09:06

37

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/

edited Aug 29 '18 at 09:06

Robert

39,162
17
99
152

answered Dec 01 '11 at 14:44

pingw33n

12,292
2
37
38

Thanks. Are you saying I should use eval() like you've shown, or should do it client side with find/update to avoid blocking ? – Brad Dec 01 '11 at 14:50
1

@Brad you should evaluate the overall cost of this particular update operation. If the operation is going to be long (many docs in the collections will be updated) you should never use eval. There's no big overhead to first query a document and then update it instead of in-place update. Especially if you limit fields queried for big documents and use covered index feature (http://www.mongodb.org/display/DOCS/Retrieving+a+Subset+of+Fields#RetrievingaSubsetofFields-CoveredIndexes) – pingw33n Dec 01 '11 at 14:56
1

Cool, thanks. I see also eval() doesn't work with sharding, so that rules it out for me anyway long term. Nolock is an interesting addition. – Brad Dec 01 '11 at 14:57
This isn't atomic. See Salvador's answer using `$mul` for an atomic solution. – JohnnyHK Oct 24 '18 at 00:52

Salvador Dali · Answer 2 · 2016-07-25T19:37:12.437

23

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 } }
);

edited Jul 25 '16 at 19:37

answered Nov 06 '13 at 23:29

Salvador Dali

214,103
147
703
753

score 5 · Answer 3 · answered Jun 12 '19 at 06:22

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.

score -1 · Answer 4 · answered Dec 01 '11 at 15:01

-1

Well, this is possible with an atomic operation as $set.

You have several options :

use the eval() solution proposed by pingw33n
retrieve the document you want to modify to get the current value and modify it with a set
if you have a high operation rate, you might want to be sure the focument has not changed during you fetch its value (using the previous solution) so you might want to use a findAndModify (see this page to get inspired on how to do it) operation.

It really depends on your context : with a very low pressure on the db, I'd go for the solution of pingw33n. With a very high operation rate, I'd use the third solution.

answered Dec 01 '11 at 15:01

kamaradclimber

2,479
1
26
45

Thanks. I don't see how findAndModify would allow using an expression though, which was my main goal. – Brad Dec 01 '11 at 15:06
@Brad findAndModify does allow to use expression but it allows to make a two phase action : first fetch the item and then update it (with your 50% reduction) only if it has not changed since retrieved – kamaradclimber Dec 01 '11 at 17:12

Multiply field by value in Mongodb

4 Answers4

Linked