How to replace string in all documents in Mongo

Question

I need to replace a string in certain documents. I have googled this code, but it unfortunately does not change anything. I am not sure about the syntax on the line bellow:

pulpdb = db.getSisterDB("pulp_database");
var cursor = pulpdb.repos.find();
while (cursor.hasNext()) {
  var x = cursor.next();
  x['source']['url'].replace('aaa', 'bbb'); // is this correct?
  db.foo.update({_id : x._id}, x);
}

I would like to add some debug prints to see what the value is, but I have no experience with MongoDB Shell. I just need to replace this:

{ "source": { "url": "http://aaa/xxx/yyy" } }

with

{ "source": { "url": "http://bbb/xxx/yyy" } }

Answer 1

It doesn't correct generally: if you have string http://aaa/xxx/aaa (yyy equals to aaa) you'll end up with http://bbb/xxx/bbb. But if you ok with this, code will work.

To add debug info use print function:

var cursor = db.test.find();
while (cursor.hasNext()) {
  var x = cursor.next();
  print("Before: "+x['source']['url']);
  x['source']['url'] = x['source']['url'].replace('aaa', 'bbb');
  print("After: "+x['source']['url']);
  db.test.update({_id : x._id}, x);
}

(And by the way, if you want to print out objects, there is also printjson function)

Answer 2

The best way to do this if you are on MongoDB 2.6 or newer is looping over the cursor object using the .forEach method and update each document usin "bulk" operations for maximum efficiency.

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

db.collection.find().forEach(function(doc) {
    print("Before: "+doc.source.url);
    bulk.find({ '_id': doc._id }).update({
        '$set': { 'source.url': doc.source.url.replace('aaa', 'bbb') }
    })
    count++;
    if(count % 200 === 0) {
        bulk.execute();
        bulk = db.collection.initializeOrderedBulkOp();
    }

// Clean up queues
if (count > 0) 
    bulk.execute();

---

From MongoDB 3.2 the Bulk() API and its associated methods are deprecated you will need to use the db.collection.bulkWrite() method.

You will need loop over the cursor, build your query dynamically and $push each operation to an array.

var operations = [];
db.collection.find().forEach(function(doc) {
    print("Before: "+doc.source.url);
    var operation = {
        updateOne: { 
            filter: { '_id': doc._id }, 
            update: { 
                '$set': { 'source.url': doc.source.url.replace('aaa', 'bbb') }
            }
        }
    };
    operations.push(operation);
})
operations.push({ 
    ordered: true, 
    writeConcern: { w: "majority", wtimeout: 5000 } 
})

db.collection.bulkWrite(operations);

Answer 3

Nowadays,

• starting Mongo 4.2, db.collection.updateMany (alias of db.collection.update) can accept an aggregation pipeline, finally allowing the update of a field based on its own value.
• starting Mongo 4.4, the new aggregation operator $replaceOne makes it very easy to replace part of a string.

// { "source" : { "url" : "http://aaa/xxx/yyy" } }
// { "source" : { "url" : "http://eee/xxx/yyy" } }
db.collection.updateMany(
  { "source.url": { $regex: /aaa/ } },
  [{
    $set: { "source.url": {
      $replaceOne: { input: "$source.url", find: "aaa", replacement: "bbb" }
    }}
  }]
)
// { "source" : { "url" : "http://bbb/xxx/yyy" } }
// { "source" : { "url" : "http://eee/xxx/yyy" } }

• The first part ({ "source.url": { $regex: /aaa/ } }) is the match query, filtering which documents to update (the ones containing "aaa")
• The second part ($set: { "source.url": {...) is the update aggregation pipeline (note the squared brackets signifying the use of an aggregation pipeline):
  • $set is a new aggregation operator (Mongo 4.2) which in this case replaces the value of a field.
  • The new value is computed with the new $replaceOne operator. Note how source.url is modified directly based on the its own value ($source.url).

Note that this is fully handled server side which won't allow you to perform the debug printing part of your question.

Answer 4

MongoDB can do string search/replace via mapreduce. Yes, you need to have a very special data structure for it -- you can't have anything in the top keys but you need to store everything under a subdocument under value. Like this:

{
    "_id" : ObjectId("549dafb0a0d0ca4ed723e37f"),
    "value" : {
            "title" : "Top 'access denied' errors",
            "parent" : "system.admin_reports",
            "p" : "\u0001\u001a%"
    }
}

Once you have this neatly set up you can do:

$map = new \MongoCode("function () {
  this.value['p'] = this.value['p'].replace('$from', '$to');
  emit(this._id, this.value);
}");
$collection = $this->mongoCollection();
// This won't be called.
$reduce = new \MongoCode("function () { }");
$collection_name = $collection->getName();
$collection->db->command([
  'mapreduce' => $collection_name,
  'map' => $map,
  'reduce' => $reduce,
  'out' => ['merge' => $collection_name],
  'query' => $query,
  'sort' => ['_id' => 1],
]);