sql - How can we write following mysql query in mongodb? I need to search a text by ignoring some characters from username field stored in users collection

Question

SELECT * FROM users WHERE REPLACE(username, '.', '_') = 'username_without_dots'

I read about $replaceAll but it is not helping.

In my query, first I am replacing existing record and then comparing, e.g there are usernames like

harry.potter

j.k rowling,

robert downy jr.

so what will happen is that DOTs(.) in these names will be replaced by UNDERSCORE( _ ) so the result will be:

harry_potter

j_k rowling,

robert downy jr_

after that comparing with searched string.

So lets say I am searching harry_potter

SELECT * FROM users WHERE REPLACE(username, '.', '_') = 'harry_potter'

Output will be: harry.potter

Answer 1

Maybe try this:

db.users.find({$and:[{"username":/^[^.]*$/},{"username":"username_without_dots"} ] } )