Update field with another field's value in the document

Question

I have a collection t1 with the following fields in its schema

_id, field1, field1

I want set field2's value field1 like sql:

update t1 set field1=field2;

How do I do it in MongoDB?

Answer 1

Good and bad news here.

Bad news is that AFAIK you can't do it with a single update() call - mongo doesn't support referring to current object in update.

Good news is that there are other ways to do it, e.g. you can run a forEach loop:

db.item.find(conditions...).snapshot().forEach( function (doc) {
  doc.field1 = doc.field2; 
  db.item.save(doc); 
});

You can run forEach in the admin shell ('mongo' command), or through some of the methods of your specific driver (e.g. in PHP I'd expect it to work with mongodb.execute() as described in here: http://www.php.net/manual/en/mongodb.execute.php)

Answer 2

Starting from version 3.4, we can use the $addFields aggregation pipeline operator to this without client side processing which is the most efficient way.

db.collection.aggregate(
    [
        { "$addFields": { "field2": "$field1" }},
        { "$out": "collection" }
    ]
)

---

Prior to version 3.4 we need to iterate the Cursor object and use $set operator to add the new field with the existing "field1" value. You need to do this using "bulk" operation for maximum efficiency.

MongoDB 3.2 deprecates Bulk() and its associated methods, thus from 3.2 upwards you need to use the bulkWrite method.

var requests = [];
db.collection.find({}, { 'field1': 1 } ).snapshot().forEach(document => { 
    requests.push( { 
        'updateOne': {
            'filter': { '_id': document._id },
            'update': { '$set': { 'field2': document.field1 } }
        }
    });
    if (requests.length === 1000) {
        //Execute per 1000 operations and re-init
        db.collection.bulkWrite(requests);
        requests = [];
    }
});

if(requests.length > 0) {
    db.collection.bulkWrite(requests);
}

---

From version 2.6 to 3.0 you can use the Bulk API.

var bulk = db.collection.initializeUnorderedBulOp();
var count = 0;

db.collection.find({}, { 'field1': 1 }).snapshot().forEach(function(document) { 
    bulk.find({ '_id': document._id }).updateOne( {
        '$set': { 'field2': document.field1 }
    });
    count++;
    if(count%1000 === 0) {
        // Excecute per 1000 operations and re-init
        bulk.execute();
        bulk = db.collection.initializeUnorderedBulkOp();
    }
})

// clean up queues
if(count > 0) {
    bulk.execute();
}

Answer 3

This can be done through:

db.nameOfCollection.find().forEach(
    function (elem) {
        db.nameOfCollection.update(
            {
                _id: elem._id
            },
            {
                $set: {
                    field2: elem.field1
                }
            }
        );
    }
);