Update MongoDB field using value of another field

In MongoDB, is it possible to update the value of a field using the value from another field? The equivalent SQL would be something like:

UPDATE Person SET Name = FirstName + ' ' + LastName

And the MongoDB pseudo-code would be:

db.person.update( {}, { $set : { name : firstName + ' ' + lastName } );

Solution 1:

The best way to do this is in version 4.2+ which allows using of aggregation pipeline in the update document and the updateOne, updateMany or update collection methods. Note that the latter has been deprecated in most if not all languages drivers.

MongoDB 4.2+

Version 4.2 also introduced the $set pipeline stage operator which is an alias for $addFields. I will use $set here as it maps with what we are trying to achieve.

db.collection.<update method>(
    {},
    [
        {"$set": {"name": { "$concat": ["$firstName", " ", "$lastName"]}}}
    ]
)

Note that square brackets in the second argument to the method which defines an aggregation pipeline instead of a plain update document. Using a plain document will not work correctly.

MongoDB 3.4+

In 3.4+ you can use $addFields and the $out aggregation pipeline operators.

db.collection.aggregate(
    [
        { "$addFields": { 
            "name": { "$concat": [ "$firstName", " ", "$lastName" ] } 
        }},
        { "$out": "collection" }
    ]
)

Note that this does not update your collection but instead replaces the existing collection or creates a new one. Also for update operations that require "typecasting" you will need client-side processing, and depending on the operation, you may need to use the find() method instead of the .aggreate() method.

MongoDB 3.2 and 3.0

The way we do this is by $projecting our documents and using the $concat string aggregation operator to return the concatenated string. From there, you then iterate the cursor and use the $set update operator to add the new field to your documents using bulk operations for maximum efficiency.

Aggregation query:

var cursor = db.collection.aggregate([ 
    { "$project":  { 
        "name": { "$concat": [ "$firstName", " ", "$lastName" ] } 
    }}
])

MongoDB 3.2 or newer

from this, you need to use the bulkWrite method.

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

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

MongoDB 2.6 and 3.0

From this version, you need to use the now deprecated Bulk API and its associated methods.

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

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

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

MongoDB 2.4

cursor["result"].forEach(function(document) {
    db.collection.update(
        { "_id": document._id }, 
        { "$set": { "name": document.name } }
    );
})

Solution 2:

You should iterate through. For your specific case:

db.person.find().snapshot().forEach(
    function (elem) {
        db.person.update(
            {
                _id: elem._id
            },
            {
                $set: {
                    name: elem.firstname + ' ' + elem.lastname
                }
            }
        );
    }
);

Solution 3:

Apparently there is a way to do this efficiently since MongoDB 3.4, see styvane's answer.

Obsolete answer below

You cannot refer to the document itself in an update (yet). You'll need to iterate through the documents and update each document using a function. See this answer for an example, or this one for server-side eval().

Solution 4:

For a database with high activity, you may run into issues where your updates affect actively changing records and for this reason I recommend using snapshot()

db.person.find().snapshot().forEach( function (hombre) {
    hombre.name = hombre.firstName + ' ' + hombre.lastName; 
    db.person.save(hombre); 
});

http://docs.mongodb.org/manual/reference/method/cursor.snapshot/