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?

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

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

Solution 3

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

Solution 4

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/

Solution 5

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

View more solutions

Comments

[Aman]

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

[Himel Nag Rana]

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

[JMess]

@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.

[Pirai Sudie]

please explain for other user too??

[Paul]

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

[Arthur Tacca]

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

[Hassan Faghihi]

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

[Mirko]

The vars should be const.

[dimid]

Thanks, can it be used with regex in find?

[dhalfageme]

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

[David Siegal]

@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.

[user3181125]

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

[rugby2312]

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

[Akash Jp]

Kudos to Xavier for awesome explanation!

[Akash Jp]

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.