Update with expression instead of value

Question

I am totally new to MongoDB... I am missing a "newbie" tag, so the experts would not have to see this question.

I am trying to update all documents in a collection using an expression. The query I was expecting to solve this was:

db.QUESTIONS.update({}, { $set: { i_pp : i_up * 100 - i_down * 20 } }, false, true);

That, however, results in the following error message:

ReferenceError: i_up is not defined (shell):1

At the same time, the database did not have any problem with eating this one:

db.QUESTIONS.update({}, { $set: { i_pp : 0 } }, false, true);

Do I have to do this one document at a time or something? That just seems excessively complicated.

Update Thank you Sergio Tulentsev for telling me that it does not work. Now, I am really struggling with how to do this. I offer 500 Profit Points to the helpful soul, who can write this in a way that MongoDB understands. If you register on our forum I can add the Profit Points to your account there.

Answer 1

I just came across this while searching for the MongoDB equivalent of SQL like this:

update t
set c1 = c2
where ...

Sergio is correct that you can't reference another property as a value in a straight update. However, db.c.find(...) returns a cursor and that cursor has a forEach method:

Queries to MongoDB return a cursor, which can be iterated to retrieve results. The exact way to query will vary with language driver. Details below focus on queries from the MongoDB shell (i.e. the mongo process).

The shell find() method returns a cursor object which we can then iterate to retrieve specific documents from the result. We use hasNext() and next() methods for this purpose.

for( var c = db.parts.find(); c.hasNext(); ) {
   print( c.next());
}

Additionally in the shell, forEach() may be used with a cursor:

db.users.find().forEach( function(u) { print("user: " + u.name); } );

So you can say things like this:

db.QUESTIONS.find({}, {_id: true, i_up: true, i_down: true}).forEach(function(q) {
    db.QUESTIONS.update(
        { _id: q._id },
        { $set: { i_pp: q.i_up * 100 - q.i_down * 20 } }
    );
});

to update them one at a time without leaving MongoDB.

If you're using a driver to connect to MongoDB then there should be some way to send a string of JavaScript into MongoDB; for example, with the Ruby driver you'd use eval:

connection.eval(%q{
    db.QUESTIONS.find({}, {_id: true, i_up: true, i_down: true}).forEach(function(q) {
        db.QUESTIONS.update(
            { _id: q._id },
            { $set: { i_pp: q.i_up * 100 - q.i_down * 20 } }
        );
    });
})

Other languages should be similar.

Answer 2

You can't use expressions in updates. Or, rather, you can't use expressions that depend on fields of the document. Simple self-containing math expressions are fine (e.g. 2 * 2).

If you want to set a new field for all documents that is a function of other fields, you have to loop over them and update manually. Multi-update won't help here.

Answer 3

//the only differnce is to make it look like and aggregation pipeline
db.table.updateMany({}, [{
      $set: {
        col3:{"$sum":["$col1","$col2"]}
      },
    }]
 )

Answer 4

Rha7 gave a good idea, but the code above is not work without defining a temporary variable.

This sample code produces an approximate calculation of the age (leap years behinds the scene) based on 'birthday' field and inserts the value into suitable field for all documents not containing such:

db.employers.find({age: {$exists: false}}).forEach(function(doc){
    var new_age = parseInt((ISODate() - doc.birthday)/(3600*1000*24*365));
    db.employers.update({_id: doc._id}, {$set: {age: new_age}});
});