Removing all spaces in a string field value in a MongoDB collection

user_350193 0
Viewed 1378

I have a mongodb collection named "users" with a few thousand users. Due to lack of validation users were able to create "username" with spaces in it. I.e, user was able to create username such as "I am the best" or " I am the best" or "I am the best " and so on. Since "username" field was not used in any form in the system it was just ok until now.

From now on the client wants to use "username" field finally, that is, to make urls such as "https://example.com/profile/{username}".

The problem is this that the "username" field values have spaces at the beginning, middle and at the end as shown above, on random. So I want to remove them using a query.

I am able to list all users using:

db.users.find({username:{ "$regex" : ".*[^\S].*" , "$options" : "i"}}).pretty();

What is the best approach to remove all spaces in username field and save them back? I am not sure how to update them in a single query.

Help is appreciated!

Ps. I actually need to write a code block to replace these usernames while checking for "existing" usernames so that there is no duplicate but I would still want to know how I do it if I need to do it using mongodb query.

mongodb-query mongodb collections replace regex
1 Answers

The problem is this that the "username" field values have spaces at the beginning, middle and at the end as shown above, on random. So I want to remove them using a query.

MongoDB 4.4 or Above:

You can use update with aggregation pipeline starting from MongoDB 4.2,

  • $replaceAll starting from MongoDB 4.4
  • it will find white space and replace with blank
db.users.update(
  { username: { $regex: " " } },
  [{
    $set: {
      username: {
        $replaceAll: {
          input: "$username",
          find: " ",
          replacement: ""
        }
      }
    }
  }],
  { multi: true }
)

Playground

MongoDB 4.2 or Above:

You can use update with aggregation pipeline starting from MongoDB 4.2,

  • $trim to remove white space from both left and right
  • $split to split username by space and result array
  • $reduce to iterate loop of above split result
  • $concat to concat username
db.users.update(
  { username: { $regex: " " } },
  [{
    $set: {
      username: {
        $reduce: {
          input: { $split: [{ $trim: { input: "$username" } }, " "] },
          initialValue: "",
          in: { $concat: ["$$value", "$$this"] }
        }
      }
    }
  }],
  { multi: true }
)

Playground

MongoDB 3.6 or Above:

  • find all users and loop through forEach
  • replace to apply pattern to remove white space, you can update pattern as per your requirement
  • updateOne to update updated username
db.users.find({ username: { $regex: " " } }, { username: 1 }).forEach(function(user) {
  let username = user.username.replace(/\s/g, "");
  db.users.updateOne({ _id: user._id }, { $set: { username: username } });
})
user_89871280
Related