It is not currently possible for an update in MongoDB to refer to the existing value of a current field when applying the update. So you are going to have to loop:
db.collection.find({},{ "category": 1 }).forEach(function(doc) {
doc.category = doc.category.trim();
db.collection.update(
{ "_id": doc._id },
{ "$set": { "category": doc.category } }
);
})
Noting the use of the $set
operator there and the projected "category" field only in order to reduce network traffic"
You might limit what that processes with a $regex
to match:
db.collection.find({
"$and": [
{ "category": /^s+/ },
{ "category": /s+$/ }
]
})
Or even as pure $regex
without the use of $and
which you only need in MongoDB where multiple conditions would be applied to the same field. Otherwise $and
is implicit to all arguments:
db.collection.find({ "category": /^s+|s+$/ })
Which restricts the matched documents to process to only those with leading or trailing white-space.
If you are worried about the number of documents to look, bulk updating should help if you have MongoDB 2.6 or greater available:
var batch = [];
db.collection.find({ "category": /^s+|s+$/ },{ "category": 1 }).forEach(
function(doc) {
batch.push({
"q": { "_id": doc._id },
"u": { "$set": { "category": doc.catetgory.trim() } }
});
if ( batch.length % 1000 == 0 ) {
db.runCommand("update", batch);
batch = [];
}
}
);
if ( batch.length > 0 )
db.runCommand("update", batch);
Or even with the bulk operations API for MongoDB 2.6 and above:
var counter = 0;
var bulk = db.collection.initializeOrderedBulkOp();
db.collection.find({ "category": /^s+|s+$/ },{ "category": 1}).forEach(
function(doc) {
bulk.find({ "_id": doc._id }).update({
"$set": { "category": doc.category.trim() }
});
counter = counter + 1;
if ( counter % 1000 == 0 ) {
bulk.execute();
bulk = db.collection.initializeOrderedBulkOp();
}
}
);
if ( counter > 1 )
bulk.execute();
Best done with bulkWrite()
for modern API's which uses the Bulk Operations API ( technically everything does now ) but actually in a way that is safely regressive with older versions of MongoDB. Though in all honesty that would mean prior to MongoDB 2.6 and you would be well out of coverage for official support options using such a version. The coding is somewhat cleaner for this:
var batch = [];
db.collection.find({ "category": /^s+|s+$/ },{ "category": 1}).forEach(
function(doc) {
batch.push({
"updateOne": {
"filter": { "_id": doc._id },
"update": { "$set": { "category": doc.category.trim() } }
}
});
if ( batch.legth % 1000 == 0 ) {
db.collection.bulkWrite(batch);
batch = [];
}
}
);
if ( batch.length > 0 ) {
db.collection.bulkWrite(batch);
batch = [];
}
Which all only send operations to the server once per 1000 documents, or as many modifications as you can fit under the 64MB BSON limit.
As just a few ways to approach the problem. Or update your CSV file first before importing.