javascript - Is there a mongo replace method/operator?

Question

In SQL I can do

UPPER(REPLACE(field.name, ' ', '')) LIKE '%" . $input . "%'

which will remove whitespaces and and will convert the string to upper case before comparing it with $input.

Is there a way to do this with mongodb?

Answer 1

The only way I know is to use $where Queries. Consider the example below:

First insert some test data

db.likecoll.insert({"name" : "John Smith"})
db.likecoll.insert({"name" : "Jo hn Smith "})
db.likecoll.insert({"name" : "JohnSmith"})
db.likecoll.insert({"name" : "JohnNOSmith"})

Then run this query to replace spaces on the fly for name field

db.likecoll.find({"$where" : "return this.name.replace(new RegExp(' ', 'g'), '') == 'JohnSmith'" })

The result is

{ "_id" : ObjectId("52f5459eb08622ca2b16ede9"), "name" : "John Smith" }
{ "_id" : ObjectId("52f545adb08622ca2b16edea"), "name" : "Jo hn Smith " }
{ "_id" : ObjectId("52f545b8b08622ca2b16edeb"), "name" : "JohnSmith" }

This should work for you but personally I don't like this approach, mostly becuase this is slow comparing to queries which can be covered with an index. How ever this is also true about your SQL server query.

EDIT:

To convert a string to upper case use str.toUpperCase() javascript function.

Hope it helps!

There is a feature request for this: https://jira.mongodb.org/browse/SERVER-829