How to replace string in collection array with MongoDB 4.2

Question

I have below Mongo Collection

{
   "name":"test",
   "work":"BA",
   "contacts":[
      {
         "company":"xyz",
         "email":"http://www.google.com/check/com/2"
      },
      {
         "company":"xyz1",
         "email":"http://www.google.com/verify/com/4"
      }
   ]
}

I want to replace www.google.com from contacts email to www.test.com, Not complete URL only a particular string from email. Any help appreciated!!

R2D2 · Accepted Answer · 2022-06-03 12:12:57Z

1

4.4+ :

db.collection.update({
 "contacts.email": {
  $regex: "google"
 }
 },
[
 {
   $addFields: {
     contacts: {
       $map: {
         input: "$contacts",
         as: "c",
         in: {
           $mergeObjects: [
            {
              email: {
              $replaceOne: {
                input: "$$c.email",
                find: "www.google.com",
                replacement: "www.test.com"
              },    
             }
            },
            {
               company: "$$c.company"
            }
           ]
          }
         }
        }
       }
      }
     ])

Explained:

Update via aggregation pipeline using replaceOne via $map/mergeObjects

playground

edited Jun 3, 2022 at 12:12

answered Mar 18, 2022 at 20:21

R2D2

10.9k2 gold badges20 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Chris Maes Over a year ago

according to the documentation, $replaceOne exists only since mongodb 4.4, not 4.2: mongodb.com/docs/manual/reference/operator/aggregation/…

R2D2 Over a year ago

Completely correct remark , fixed immediatelly... :)

Takis · Accepted Answer · 2022-03-18 20:02:10Z

0

Query1

$indexOfCP MongoDB >=3.6

PlayMongo

aggregate(
[{"$set": {"m": "www.google.com", "r": "www.test.com"}},
 {"$set": 
   {"contacts": 
     {"$map": 
       {"input": "$contacts",
        "in": 
         {"$mergeObjects": 
           ["$$this",
             {"email": 
               {"$let": 
                 {"vars": {"index": {"$indexOfCP": ["$$this.email", "$m"]}},
                  "in": 
                   {"$cond": 
                     [{"$eq": ["$$index", -1]}, "$$this.email",
                       {"$concat": 
                         [{"$substrCP": 
                             ["$$this.email", 0, {"$subtract": ["$$index", 0]}]},
                          "$r",
                           {"$substrCP": 
                             ["$$this.email",
                               {"$add": ["$$index", {"$strLenCP": "$m"}]},
                               {"$subtract": 
                                 [{"$strLenCP": "$$this.email"},
                                   {"$add": 
                                     ["$$index", {"$strLenCP": "$m"}]}]}]}]}]}}}}]}}}}},
 {"$unset": ["m", "r"]}])

Query2

$replaceAll MongoDB >= 4.4

PlayMongo

aggregate(
[{"$set": 
   {"contacts": 
     {"$map": 
       {"input": "$contacts",
        "in": 
         {"$mergeObjects": 
           ["$$this",
             {"email": 
               {"$replaceAll": 
                 {"input": "$$this.email",
                  "find": "www.google.com",
                  "replacement": "www.test.com"}}}]}}}}}])

answered Mar 18, 2022 at 20:02

Takis

8,7552 gold badges17 silver badges28 bronze badges

9 Comments

pmv Over a year ago

Hi Takis, Does above query 1 code work for below URL: test-test1-test2.ctest.com/ket/set instead of "www.google.com"

Takis Over a year ago

the first query that is for 4.2, find the index of first substring and replace it, if you mean this its working. It can fail only if you have that string more than 1 time (in this case only the first occurence will be replaced).

pmv Over a year ago

First query is working I am getting result, sorry! I want to update the collection also with first query.

Takis Over a year ago

its the same, you can use pipeline update like this

pmv Over a year ago

Yes it works, Thanks Takis but it append where we don't have contacts array in collections and it append with null like "contacts: null"

|

Collectives™ on Stack Overflow

How to replace string in collection array with MongoDB 4.2

2 Answers 2

2 Comments

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related