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?

score 153 · Accepted Answer · edited Jun 20 '16 at 14:54

153

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

edited Jun 20 '16 at 14:54

nilsi

10,351
10
67
79

answered Jul 07 '15 at 07:50

Naveed

1,979
1
13
20

3

can you please elaborate it? how it is working what is the mean of the code? for other users too? – Aman Jun 24 '17 at 19:49
4

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)); })```` – Himel Nag Rana Oct 11 '17 at 06:17
3

@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. – JMess Apr 07 '18 at 01:04
please explain for other user too?? – Pirai Sudie Oct 16 '18 at 10:49
Surely it would be possible to just update the one field that has changed rather than reading and writing back the whole document? – Arthur Tacca Mar 28 '19 at 16:57
i think it's last resolve to my https://dba.stackexchange.com/questions/241058/can-i-use-a-custom-function-with-updateone-or-updatemany-in-mongodb-shell question :( – Hassan Faghihi Jun 22 '19 at 09:38
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. – Akash Jp May 03 '22 at 15:18

Xavier Guihot · Answer 2 · 2020-02-22T11:18:48.347

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 ] }
      ]
    }}
  }]
)

Thanks, are the slashes ("/") rerquired on the replacement ? — dhalfageme, Aug 04 '20 at 08:42
@dhalfageme 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. — David Siegal, Oct 20 '20 at 05:38
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. — Carcigenicate, Oct 13 '22 at 14:45

score 9 · Answer 3 · answered Apr 02 '19 at 04:30

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:mystrongpassword@10.148.0.7:27017,10.148.0.8:27017,10.148.0.9:27017/my-dbs?replicaSet=rs0&authSource=admin" --collection=products --out=data/

I was looking for a solution like that for an Ops man, pretty smart! Thank you! — Denis Trofimov, Nov 18 '22 at 18:32

score 8 · Answer 4 · edited May 23 '17 at 11:54

8

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

edited May 23 '17 at 11:54

Community

1
1

answered Sep 25 '12 at 20:37

Louisa

861
6
5

score 6 · Answer 5 · answered Aug 14 '18 at 17:31

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);
});

answered Aug 14 '18 at 17:31

Michael Michelis

139
2
7

4

The vars don't need to be in the loop so put them before. – Paul Feb 06 '19 at 22:05
The vars should be const. – Mirko Nov 28 '19 at 16:26

score 5 · Answer 6 · answered Jul 25 '16 at 18:42

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});
  });
});

score 4 · Answer 7 · answered Oct 11 '17 at 06:20

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));
})

score 2 · Answer 8 · answered Nov 03 '15 at 12:58

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!

is there any way to replace partial substring of a large binary string in mongodb ? — user3181125, Nov 05 '21 at 04:24

score 2 · Answer 9 · answered Dec 24 '19 at 01:37

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/" }
>

score 2 · Answer 10 · answered Jul 09 '20 at 08:31

2

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);
        }
    })

answered Jul 09 '20 at 08:31

Rafeeque

845
9
13

This is particularly helpful on the older MongoDB versions like 3.6 where there are limited operators to use. thanks buddy ! – rugby2312 Mar 18 '22 at 09:26

Ravi Tyagi · Answer 11 · 2022-08-22T06:52:40.953

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

filetranscoding -- Collection Name
profiles -- ColumnName in which you want to update
/N_/ -- String which you are searching (where Condition )
find:"N_",replacement: "" -- N_ which u want to remove "" from which you want to remove here we are taking blank String

How to replace substring in mongodb document

11 Answers11

Using mongodump,bsondump and mongoimport.

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

Linked

Related