how to use variable in update statement - MongoDB

Question

i am trying to use a variable value as column name to update all my documents in a collection. i am trying this through robo mongo shell command. code is below:

doc=db.Symbols.findOne();
for (key in doc) 
{
db.getCollection('Symbols').update(
   { key : null },
   {
        $set: { key: ""} 
   },
   { multi: true, upsert: false }
)
    }

but this code does not seems to be working for me. seems like value in variable "key" does not replace at runtime and instead of comparing with value in variable key robo mongo compare it with word "key" itself. Any idea how to do this ?

did you check **[$rename](https://docs.mongodb.com/manual/reference/operator/update/rename/)** — Neo-coder, Dec 15 '16 at 15:09
@yogesh- my concern is not to change column name but to update all column values that contains null with a empty string. — shobhit, Dec 15 '16 at 15:17
This is a javascript thing, it is treating `key` as string literal. You need to form the arrays outside manually like `crtiera[key] = null` and then use it — Sammaye, Dec 15 '16 at 15:25

score 2 · Answer 1 · answered Dec 15 '16 at 15:40

You need a mechanism which loops through the whole collection and with each iteration, check for each field within a document for null, and update the collection accordingly. A basic approach follows:

db.Symbols.find({}).forEach(function(doc){
    var update = { "$set": { } },
        query = {};
    for (var key in doc) {
        if (doc[key] === null) {
            update["$set"][key] = "";
            query[key] = { "$exists": false }
        }            
    }
    if (Object.keys(query).length !=== 0) 
        db.Symbols.updateOne(query, update);        

});

For very humongous collections, you can leverage your updates to be more efficient with the bulk API which can update your collection in bulk instead of sending the update request per document, thus making the updates a bit faster and more performant. The following example demonstrates this with the bulkWrite() method:

var ops = [];

db.Symbols.find({}).forEach(function(doc){
    var update = { "$set": { } },
        query = {};
    for (var key in doc) {
        if (doc[key] === null) {
            update["$set"][key] = "";
            query[key] = { "$exists": false }
        }            
    }
    if (Object.keys(query).length !=== 0) {
        ops.push({
            "updateOne": {
                "filter": query,
                "update": update
            }
        });
    }


    // Send to server in batches of 500 operations only
    if (ops.length % 1000 === 0) {
        db.Symbols.bulkWrite(ops);
        ops = [];
    }
})

// Clear remaining queue
if (ops.length > 0)
    db.Symbols.bulkWrite(ops);

how to use variable in update statement - MongoDB

1 Answers1