Som Poddar
Som Poddar

Reputation: 1451

Update MongoDB collection with $toLower or $toUpper

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)

Upvotes: 10

Views: 11959

Answers (3)

thiagotrss
thiagotrss

Reputation: 149

The code below do exactly what you want in one step:

db.organizations.updateMany( { state: { $exists : true } }, [ { $set: { state: { $toUpper: "$state" } } } ] );

Upvotes: 1

Neil Lunn
Neil Lunn

Reputation: 151132

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.

Upvotes: 8

Josie
Josie

Reputation: 60

You have to put toUpperCase() like this:

"$set": { "state": doc.state.toUpperCase() } }

Upvotes: 2

Related Questions