How to replace substring in mongodb document

Question

I have a lot of mongodb documents in a collection of the form:

{
....
"URL":"www.abc.com/helloWorldt/..."
.....
}

I want to replace helloWorldt with helloWorld to get:

{
....
"URL":"www.abc.com/helloWorld/..."
.....
}

How can I achieve this for all documents in my collection?

Nestle

Answer 1

db.media.find({mediaContainer:"ContainerS3"}).forEach(function(e,i) {
    e.url=e.url.replace("//a.n.com","//b.n.com");
    db.media.save(e);
});

Extemporary

can you please elaborate it? how it is working what is the mean of the code? for other users too? – Schizophrenia

Just awesome. My case was - I have a field which is an array - so I had to add an extra loop. My query is: db.getCollection("profile").find({"photos": {$ne: "" }}).forEach(function(e,i) { e.photos.forEach(function(url, j) { url = url.replace("http://a.com", "https://dev.a.com"); e.photos[j] = url; }); db.getCollection("profile").save(e); eval(printjson(e)); }) – Spoony

@doe "e" here represents a copy of each document found. That copy has its value for url (it is case sensitive, note that this is not the same as the question asker's "url") updated based upon its original value for url. "e" keeps all of its original fields with the one modified field. Saving e back into the collection overwrites the original "e". There is no need for "i" here and it can be removed from the function declaration. – Arresting

please explain for other user too?? – Sikata

Surely it would be possible to just update the one field that has changed rather than reading and writing back the whole document? – Glasgow

i think it's last resolve to my dba.stackexchange.com/questions/241058/… question :( – Weissman

This method is not the optimised approach, because it will run on javascript's single thread. If optimization is on note I would prefer Xavier's approach. – Arsenate

Answer 2

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.

// { URL: "www.abc.com/helloWorldt/..." }
// { URL: "www.abc.com/HelloWo/..." }
db.collection.updateMany(
  { URL: { $regex: /helloWorldt/ } },
  [{
    $set: { URL: {
      $replaceOne: { input: "$URL", find: "helloWorldt", replacement: "helloWorld" }
    }}
  }]
)
// { URL: "www.abc.com/helloWorld/..." }
// { URL: "www.abc.com/HelloWo/..." }

• The first part ({ URL: { $regex: /helloWorldt/ } }) is the match query, filtering which documents to update (the ones containing "helloWorldt") and is just there to make the query faster.
• The second part ($set: { 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 URL is modified directly based on the its own value ($URL).

---

Before Mongo 4.4 and starting Mongo 4.2, due to the lack of a proper string $replace operator, we have to use a bancal mix of $concat and $split:

db.collection.updateMany(
  { URL: { $regex: "/helloWorldt/" } },
  [{
    $set: { URL: {
      $concat: [
        { $arrayElemAt: [ { $split: [ "$URL", "/helloWorldt/" ] }, 0 ] },
        "/helloWorld/",
        { $arrayElemAt: [ { $split: [ "$URL", "/helloWorldt/" ] }, 1 ] }
      ]
    }}
  }]
)

Votive

Thanks, can it be used with regex in find? – Wirewove

Thanks, are the slashes ("/") rerquired on the replacement ? – Walley

@Walley I see confusion in the latter example. The forward slashes following the $regex operator (i.e. $regex: "/helloWorldt/") are regular expression delimeters, which are required. Everywhere else in the example, the forward slashes are URL path delimiters, and are likely not necessary. They'd only be necessary if the OP had URLs with a path including /helloWorldthirsty/ that they wanted to keep. – Troublesome

Kudos to Xavier for awesome explanation! – Arsenate

If you get "the update operation document must contain atomic operators", you likely need to update Mongo. I thought we were up to date, but we're actually on 3.2. – Communard

Answer 3

Using mongodump,bsondump and mongoimport.

Sometimes the mongodb collections can get little complex with nested arrays/objects etc where it would be relatively difficult to build loops around them. My work around is kinda raw but works in most scenarios regardless of complexity of the collection.

1. Export The collection using mongodump into .bson

mongodump --db=<db_name> --collection=<products> --out=data/

2. Convert .bson into .json format using bsondump

bsondump --outFile products.json data/<db_name>/products.bson

3. Replace the strings in the .json file with sed(for linux terminal) or with any other tools

sed -i 's/oldstring/newstring/g' products.json

4. Import back the .json collection with mongoimport with --drop tag where it would remove the collection before importing

mongoimport --db=<db_name>  --drop --collection products <products.json

Alternatively you can use --uri for connections in both mongoimport and mongodump

example

mongodump --uri "mongodb://mongoadmin:[email protected]:27017,10.148.0.8:27017,10.148.0.9:27017/my-dbs?replicaSet=rs0&authSource=admin" --collection=products --out=data/

Deft

I was looking for a solution like that for an Ops man, pretty smart! Thank you! – Kneehigh

Answer 4

Currently, you can't use the value of a field to update it. So you'll have to iterate through the documents and update each document using a function. There's an example of how you might do that here: MongoDB: Updating documents using data from the same document

Indihar

Answer 5

nodejs. Using mongodb package from npm

db.collection('ABC').find({url: /helloWorldt/}).toArray((err, docs) => {
  docs.forEach(doc => {
    let URL = doc.URL.replace('helloWorldt', 'helloWorld');
    db.collection('ABC').updateOne({_id: doc._id}, {URL});
  });
});

Lied

Thank you! Got inspiration and wrote this gist. – Labonte

Answer 6

To replace ALL occurrences of the substring in your document use:

db.media.find({mediaContainer:"ContainerS3"}).forEach(function(e,i) {
var find = "//a.n.com";
var re = new RegExp(find, 'g');
e.url=e.url.replace(re,"//b.n.com");
db.media.save(e);
});

Boorman

The vars don't need to be in the loop so put them before. – Tuff

The vars should be const. – Aloeswood

Answer 7

The formatting of my comment to the selected answer (@Naveed's answer) has got scrambled - so adding this as an answer. All credit goes to Naveed.

----------------------------------------------------------------------

Just awesome. My case was - I have a field which is an array - so I had to add an extra loop.

My query is:

db.getCollection("profile").find({"photos": {$ne: "" }}).forEach(function(e,i) {
    e.photos.forEach(function(url, j) {
        url = url.replace("http://a.com", "https://dev.a.com");
        e.photos[j] = url;
    });
    db.getCollection("profile").save(e);
    eval(printjson(e));
})

Spoony

Answer 8

Now you can do it!

We can use Mongo script to manipulate data on the fly. It works for me!

I use this script to correct my address data.

Example of current address: "No.12, FIFTH AVENUE,".

I want to remove the last redundant comma, the expected new address ""No.12, FIFTH AVENUE".

var cursor = db.myCollection.find().limit(100);

while (cursor.hasNext()) {
  var currentDocument = cursor.next();

  var address = currentDocument['address'];
  var lastPosition = address.length - 1;

  var lastChar = address.charAt(lastPosition);

  if (lastChar == ",") {

    var newAddress = address.slice(0, lastPosition);


    currentDocument['address'] = newAddress;

    db.localbizs.update({_id: currentDocument._id}, currentDocument);

  }
}

Hope this helps!

Lowbrow

is there any way to replace partial substring of a large binary string in mongodb ? – Islek

Answer 9

This can be done by using the Regex in the first part of the method replace and it will replace the [all if g in regex pattern] occurrence(s) of that string with the second string, this is the same regex as in Javascript e.g:

const string = "www.abc.com/helloWorldt/...";
console.log(string);
var pattern = new RegExp(/helloWorldt/)
replacedString = string.replace(pattern, "helloWorld");
console.log(replacedString);

Since the regex is replacing the string, now we can do this is MongoDB shell easily by finding and iterating with each element by the method forEach and saving one by one inside the forEach loop as below:

> db.media.find()
{ "_id" : ObjectId("5e016628a16075c5bd26fbe3"), "URL" : "www.abc.com/helloWorld/" }
{ "_id" : ObjectId("5e016701a16075c5bd26fbe4"), "URL" : "www.abc.com/helloWorldt/" }
> 
> db.media.find().forEach(function(o) {o.URL = o.URL.replace(/helloWorldt/, "helloWorld"); printjson(o);db.media.save(o)})
{
    "_id" : ObjectId("5e016628a16075c5bd26fbe3"),
    "URL" : "www.abc.com/helloWorld/"
}
{
    "_id" : ObjectId("5e016701a16075c5bd26fbe4"),
    "URL" : "www.abc.com/helloWorld/"
}
> db.media.find()
{ "_id" : ObjectId("5e016628a16075c5bd26fbe3"), "URL" : "www.abc.com/helloWorld/" }
{ "_id" : ObjectId("5e016701a16075c5bd26fbe4"), "URL" : "www.abc.com/helloWorld/" }
>

Bergson

Answer 10

If you want to search for a sub string, and replace it with another, you can try like below,

    db.collection.find({ "fieldName": /.*stringToBeReplaced.*/ }).forEach(function(e, i){
        if (e.fieldName.indexOf('stringToBeReplaced') > -1) {
          e.content = e.content.replace('stringToBeReplaced', 'newString');
          db.collection.update({ "_id": e._id }, { '$set': { 'fieldName': e.fieldName} }, false, true);
        }
    }) 

Eolanda

This is particularly helpful on the older MongoDB versions like 3.6 where there are limited operators to use. thanks buddy ! – Marillin

Answer 11

db.filetranscoding.updateMany({ profiles: { $regex: /N_/ } },[{$set: { profiles: {$$replaceAll: { input: "$profiles", find:"N_",replacement: "" }},"status":"100"}}])

1. filetranscoding -- Collection Name

2. profiles -- ColumnName in which you want to update

3. /N_/ -- String which you are searching (where Condition )

4. find:"N_",replacement: "" -- N_ which u want to remove "" from which you want to remove here we are taking blank String

Gradation