Update MongoDB collection with $toLower or $toUpper

Question

I would like to convert the 'state' field for all 'Organization' to all UPPER case. So

'Ky' becomes 'KY' 'tX' becomes 'TX' 'ca' becomes 'CA'

why this doesn't work

db.organizations.update(state:{ $exists : true }},{$set:{state:{ $toUpper : state }}}, false, true)

Answer 1

The $toLower and $toUpper operators you reference are for use with the aggregation framework only, and by itself does not alter documents in a collection as the .update() statement does. Also it is not presently possible to reference the value of an existing field within an update statement to produce a new value.

What you need to do is "loop" the collection and make your changes:

db.organizations.find({ "state": { "$exists": true } }).forEach(function(doc) {
    db.organizations.update(
        { "_id": doc._id },
        { "$set": { "state": doc.state.toUpperCase() } }
    );
});

With MongoDB 2.6 or greater you can make this a bit better with the bulk operations API:

var bulk = db.organizations.initializeOrderedBulkOp();
var count = 0;

db.organizations.find({ "state": { "$exists": true } }).forEach(function(doc) {
    bulk.find({ "_id": doc._id }).updateOne({
        "$set": { "state": doc.state.toUpperCase() } }
    );
    count++;
    if ( count % 500 == 0 ) {
        bulk.execute();
        bulk = db.organizations.initializeOrderedBulkOp();
        count = 0;
    }
});

if ( count > 0 )
    bulk.execute();

While still basically looping the results, the writes are only sent to the database once every 500 documents or whatever you choose to set staying under the 16MB BSON limit for the operation.