replace Regex with $replaceAll mongodb

Question

I'm trying to replace all the unnecessary text in the field

For example I have this:

_id:12345678901,
name:"Company Z"

_id:12345678902,
name: "Corp Y"

_id:12345678902,
name: "Corporation X"

And I want to remove Corp, Corporation and Company in the field name, and make a new field for it, but I can't do it with regex

Target:

_id:12345678901,
name: "Company Z",
newName: "Z"

_id:12345678902,
name: "Corp Y",
newName: "Y"

_id:12345678902,
name: "Corporation X",
newName: "X"

Currently I have this:


db.customers.updateMany(
  {  },
  [{
    $set: { newName: {
      $replaceAll: { input: "$name", find: {"$regexFind": { input: "$name", regex: '/(Corp)|(Corporation)|(Company)/gi' } }, replacement: "" }
    }}
  }]
)

But it doesn't seems to work.

BTW im using mongod 4.4.14

aaronlukacs · Accepted Answer · 2022-06-20 11:22:16Z

5

The problem is that $regexFind doesn't return a string, but an object doc First you have to do $regexFind, and after use the returned objects match field to to the $replaceAll. Here is an example aggregation pipeline which transforms your objects to the desired ones:

  {
    $addFields: {
      "regexResObject": {
        "$regexFind": {
          "input": "$name",
          "regex": "(Company )|(Corporation )|(Corp )"
        }
      }
    }
  },
  {
    "$match": {
      regexResObject: {
        $ne: null
      }
    }
  },
  {
    $addFields: {
      newName: {
        $replaceAll: {
          input: "$name",
          find: "regexResObject.match",
          replacement: ""
        }
      }
    }
  },
  {
    "$project": {
      regexResObject: 0
    }
  }
])

answered Jun 20, 2022 at 11:22

aaronlukacs

4893 silver badges8 bronze badges

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

3 Comments

Gr4y Over a year ago

it's kinda works, but some how, if name isn't contain the regex, newName is returning null. Any suggestion?

aaronlukacs Over a year ago

yes, thats why I filtered them out. you could write a conditional to add the original field if the value is null.

Dov Rosenberg Over a year ago

I don't believe you can use $match inside of $updateMany. So if you do this approach, you'll have to (bulk) write them all back. May be simpler to just do it in code, depending on database size and how many matches you think you'll get.

Dov Rosenberg · Accepted Answer · 2023-06-11 20:39:06Z

3

Building off of aaronlukacs answer, but fixing for the fact that you can't use match inside an update pipeline:

db.customers.updateMany(
  {  },
  [
    {
      $addFields: {
        regexResObject: {
          $regexFindAll: {
            input: '$name',
            regex: '(Company )|(Corporation )|(Corp )'
          }
        }
      }
    },
    {
      $addFields: {
        newName: {
          $reduce: {
            input: '$regexResObject',
            initialValue: '$name',
            in: {
              $replaceAll: {
                input: '$$value',
                find: '$$this.match',
                replacement: ''
              }
            }
          }
        }
      }
    },
    {
      $project: {
        regexResObject: 0
      }
    }
  ]
)

Note that if there are no matches, newName should just be name. I also used regexFindAll, which will eliminate more than one instance of the regex match... not relevant here but might be for other cases.

answered Jun 11, 2023 at 20:39

Dov Rosenberg

8259 silver badges26 bronze badges

1 Comment

FINDarkside Nov 26, 2024 at 10:47

This is using aggregation pipeline as well, so there's no major difference in using updateMany vs aggregate in this case.

Collectives™ on Stack Overflow

replace Regex with $replaceAll mongodb

2 Answers 2

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related