For example my data consists of :

[
   {username: "velikiye.lucky 09", region: "Hamburg"},
   {username: "Roger. blue", region: "Hamburg"},
]

If I put query to search for "roger blue" (note that I don't use the dot character in my query)

How to get {username: "roger. blue", region: "Hamburg"} in mongoDB ? Currently I had to load all data from the database, remove all non-alphanumeric characters in it, lower-case it, and then search for the requested username.

CodePudding user response：

You should use regex-match like this:

db.collection.find({
  username: {
    $regex: "(.*)roger(.*)blue(.*)",
    $options: "i"
  }
})

Playground link.

Here, (.*) will ignore any character. If you only want to ignore non-alphanumeric characters, then as you mentioned in the comments, try this:

db.collection.find({
  username: {
    $regex: "[^a-zA-Z0-9]*roger[^a-zA-Z0-9]*blue[^a-zA-Z0-9]*",
    $options: "i"
  }
})

Playground link.