MongoDB: How to replace a substring in a string (with examples)

Introduction

In this tutorial, we will explore how to replace substrings within strings in MongoDB, the popular NoSQL database. Working with strings is a common task in database management, and MongoDB offers several ways to manipulate string data. We’ll start with some basic operations and progressively move to more advanced techniques, providing code examples and outputs to demonstrate each operation.

Prerequisites

• Basic understanding of MongoDB operations.
• MongoDB server installed and running.
• Access to a MongoDB database with string data available for manipulation.

Replacing Substrings in MongoDB

MongoDB does not have a direct query to replace substrings in strings. Some operations might require a combination of MongoDB’s aggregation pipeline stages. The $replaceOne, $replaceAll, and $regex operators within the $project or $addFields stages are commonly employed in such cases.

Example Data

We’ll use the following sample collection ‘products’ for the examples:

{
  "_id": 1,
  "name": "freshApples",
  "description": "Delicious organic apples."
},
{
  "_id": 2,
  "name": "bakedGoods",
  "description": "Freshly baked bread and cookies."
},
{
  "_id": 3,
  "name": "juicyOranges",
  "description": "Sweet and juicy oranges from sunny Florida."
}

Using the $replaceOne Operator

The $replaceOne operator replaces the first occurrence of a specified substring in a string. Here’s a basic example:

db.products.aggregate([
  {
    $set: {
      'replaced_name': {
        $replaceOne: { input: '$name', find: 'fresh', replacement: 'ripe' }
      }
    }
  }
]);

This will output:

{'_id': 1, 'name': 'ripeApples' },
 {'_id': 2, 'name': 'bakedGoods' },
 {'_id': 3, 'name': 'juicyOranges' }

Using the $replaceAll Operator

The $replaceAll operator replaces all occurrences of a specified substring. If you need to change every ‘fresh’ to ‘ripe’ regardless of how many times it appears, use the following:

db.products.aggregate([
  {
    $set: {
      'replaced_description': {
        $replaceAll: { input: '$description', find: 'fresh', replacement: 'ripe' }
      }
    }
  }
]);

The output will show that all ‘fresh’ substrings in the ‘description’ field have been replaced with ‘ripe’:

{
 '_id': 1, 'description': 'Delicious organic apples.' },
 {'_id': 2, 'description': 'Ripely baked bread and cookies.' },
 {'_id': 3, 'description': 'Sweet and ripe oranges from sunny Florida.' }

Complex Substring Replacement with $regex

For more complex patterns, you can use the $regex operator. The following example demonstrates replacing a substring based on a regex pattern:

db.products.updateMany(
  {},
  [
    {
      $set: {
        'name': {
          $replaceAll: {
            input: '$name',
            find: { $regex: 'A', $options: 'i' },
            replacement: '@'
          }
        }
      }
    }
  ]
);

This command changes every word that starts with the letter ‘A’ to ‘@’. The output would look like this:

{
 '_id': 1, 'name': 'fresh@pples' },
 {'_id': 3, 'name': 'juicy@ranges' }

Advanced Substring Replacement

For advanced use-cases that involve replacing a substring within a string across documents in a MongoDB collection, you can utilize the aggregation pipeline. This example demonstrates how to filter documents, project a modified document structure, and use string operators to perform a complex replacement of a substring within a field.

Suppose you want to replace the substring “oldText” with “newText” in the description field of documents in the documents collection:

db.documents.aggregate([
  {
    // Step 1: Filter documents (optional)
    $match: {
      description: { $regex: "oldText" } // Only documents containing 'oldText' in 'description'
    }
  },
  {
    // Step 2: Project a new document structure
    $project: {
      description: {
        // Step 3: Replace 'oldText' with 'newText' in the 'description'
        $replaceAll: {
          input: "$description", // Field to perform the replacement on
          find: "oldText",       // Substring to find
          replacement: "newText" // Replacement text
        }
      },
      // Include other fields as needed
      otherField: 1
    }
  }
]);

This aggregation pipeline consists of three main stages:

1. $match: Filters the documents to only those that contain the substring “oldText” within the description field. This step is optional and helps to narrow down the documents that proceed to the subsequent stages, optimizing performance.
2. $project: Projects (or reshapes) each document in the stream. The new structure can include existing fields, exclude them, or add new fields. In this example, it’s used to modify the description field.
3. $replaceAll: Within the $project stage, the $replaceAll operator is used to replace all occurrences of “oldText” with “newText” in the description field. This operator was introduced in MongoDB 4.4. If you’re using an earlier version of MongoDB that does not support $replaceAll, you might need to use a combination of other string operators or handle the logic in your application code.

Note: This pipeline only transforms the data within the context of this aggregation query. If you need to permanently update the documents in the collection, consider using an update operation with the $set and $replaceAll (for MongoDB 4.4 and later) or appropriate logic in your application code for earlier versions.

Performance Considerations

String manipulation can be resource-intensive. When dealing with large collections, make sure to properly index the fields and consider the performance implications.

Conclusion

We have explored the basics of replacing substrings in MongoDB, advancing from simple replacements to complex regex-based operations. Always remember to exercise caution when doing any form of data manipulation and ensure consistent backups.