12

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.

Improve this question

6 Answers 6

Reset to default
9 

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

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

2 Comments

Note that if you run this without the surrounding [] (which indicate an aggregation pipeline as stated) then this will insert the literal value {$sum: ["$col1", "$col2"]} into the collection.
I get an error: MongoServerError: BSON field 'update.updates.u' is the wrong type 'array', expected type 'object' Note I'm on MongoDb 3.6.6 . Is this technique newer than that?
6

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.

Improve this answer

5 Comments

Couldn't you send a bit of db.questions.find(...).forEach(function(q) { /* db.questions.update(...) to patch 'q' in-place */ }) JavaScript into MongoDB to avoid leaving the database? I've done similar things to avoid round-tripping out of the database.
@muistooshort: Theoretically, you could. I haven't done this myself.
I just did a few tests and it seems to work, the docs even suggest that db.questions.find(...) will be a cursor so it won't try to instantiate a whole array for the forEach call.
Are you trying in the shell? I was more wondering what command I'd have to use in the (ruby) driver.
You can use db.eval() to send JavaScript straight in. I'll write it up as an answer later this evening, might be useful for future searchers.
6

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.

Improve this answer

3 Comments

eval is deprecated, what is the replacement or alternative? Simply execute it directly, without eval? Does it have the same effects?
@CaptainObvious: I don't use MongoDB anymore so I'm not sure. Does this other answer help?
Unfortunately, no. docs.mongodb.com/v3.2/reference/command states that eval is deprecated as well. Thanks for the effort though, I will try to figure it out.
3

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}});
});
Improve this answer

Comments

0

Example to remove "00" from the beginning of a caller id:

db.call_detail_records_201312.find(
    { destination: /^001/ }, 
    { "destination": true }
).forEach(function(row){
    db.call_detail_records_201312.update(
        { _id: row["_id"] },
        { $set: {
                destination: row["destination"].replace(/^001/, '1')
            }
        }
    )
});
Improve this answer

2 Comments

Just update row.destination and use row.save instead db.call...update(
I'm very sorry: the correct sintax is db.call.save(row) instead row.save
0

I did a self-referencing document expression like so on a different problem:

db.activities.find().forEach((row)=> { 
  db.activities.updateOne({
    _id: row._id
  }, {
    $set: {
      cost: row.cost * 1.35
    }
  });
  })

On Atlas free tier, this took a long time to execute, at the rate of about 35 updates / second

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.