73

I am using a case insensitive search in Mongo, something similar to https://stackoverflow.com/q/5500823/1028488.

ie I am using a regex with options i. But I am am having trouble restricting the regex to just that word, its performs more like a 'Like' in SQL

eg: if I use query like {"SearchWord" : { '$regex' : 'win', $options: '-i' }}, it shows me results for win, window & winter. How do i restrict it to jsut show win?

I tried /^win$/ but its sayin invalid Json.... Please suggest a way.

Thanks in advance

Community
  • 1
  • 1
Praneeta
  • 1,314
  • 3
  • 16
  • 20

6 Answers6

145

You can Use $options => i for case insensitive search. Giving some possible examples required for string match.

Exact case insensitive string

db.collection.find({name:{'$regex' : '^string$', '$options' : 'i'}})

Contains string

db.collection.find({name:{'$regex' : 'string', '$options' : 'i'}})

Start with string

db.collection.find({name:{'$regex' : '^string', '$options' : 'i'}})

End with string

db.collection.find({name:{'$regex' : 'string$', '$options' : 'i'}})

Doesn't Contains string

db.collection.find({name:{'$regex' : '^((?!string).)*$', '$options' : 'i'}})

Keep this as a bookmark, and a reference for any other alterations you may need. http://www.cheatography.com/davechild/cheat-sheets/regular-expressions/

Somnath Muluk
  • 46,917
  • 28
  • 204
  • 217
  • By mistake, you had put the example of "Contains string" inside "Exact case insensitive string" and vice-versa. I have edited your answer by swapping the examples for those two cases and also corrected some grammatical mistakes. – gauravparmar Feb 16 '18 at 06:53
  • @gauravparmar: Please check and correct if anything is incorrect. – Somnath Muluk Feb 16 '18 at 09:28
  • I have edited your answer but it has to be peer-reviewed, it says. – gauravparmar Feb 16 '18 at 13:51
60

You can use '$regex':'^win$' or /^win$/i (notice no quote on the second one)

Source here : Regex in queries with Mongo

Aurélien B
  • 3,950
  • 3
  • 29
  • 48
22

UPDATE: As of MongoDB 2.4 one would use a "text" index and full text search query to do this. You can read about them here. If using a recent MongoDB the approach below would be silly and unecessary.

However, if you have MongoDB < 2.4.0 you could use a regular expression like so:

> db.reg.insert({searchword: "win"})
> db.reg.insert({searchword: "window"})
> db.reg.insert({searchword: "Win"})

> db.reg.find()
{ "_id" : ObjectId("4ecd2e33dd68c9021e453d12"), "searchword" : "win" }
{ "_id" : ObjectId("4ecd2e36dd68c9021e453d13"), "searchword" : "window" }
{ "_id" : ObjectId("4ecd2e39dd68c9021e453d14"), "searchword" : "Win" }

> db.reg.find({ searchword: /^win$/i })
{ "_id" : ObjectId("4ecd2e33dd68c9021e453d12"), "searchword" : "win" }
{ "_id" : ObjectId("4ecd2e39dd68c9021e453d14"), "searchword" : "Win" }

However, your version wasn't working because you don't need the "/"s when using the $regex operator:

> db.reg.find({ searchword: { $regex: "^win$", $options: '-i' }})
{ "_id" : ObjectId("4ecd2e33dd68c9021e453d12"), "searchword" : "win" }
{ "_id" : ObjectId("4ecd2e39dd68c9021e453d14"), "searchword" : "Win" }

Please note that case insensitive queries do not use the index so it might make sense to make a lowercase searchword field so that you can speed that query up.

Go here for more info on RegularExpressions

Tyler Brock
  • 27,248
  • 15
  • 69
  • 76
  • 2
    $text isn't a replacement for regex. It is pretty restrictive and wouldn't allow him to find "winter" with a search for "win". – B T Apr 08 '16 at 21:44
  • @BT You're right. In order to do that one would have to store n-grams and match on those. [Fuzzy search with MongoDB and Python](https://medium.com/xeneta/fuzzy-search-with-mongodb-and-python-57103928ee5d#.yg7f3428b) – Rohmer Mar 28 '17 at 18:10
  • Also: [Case Insensitive Indexes](https://docs.mongodb.com/manual/core/index-case-insensitive/) – Rohmer Mar 28 '17 at 18:11
0
{
          $match: {
            $expr: {
              $and: [
                { $eq: ["$_id", "$$itemId"] },
                {
                  $regexMatch: {
                    input: "$brandData.name",
                    regex: "sample",
                    options: "i",
                  },
                },
              ],
            },
          },
        },
Hrishikesh Baidya
  • 161
  • 4
-1

For Case insensitive

db.users.find({"name":{ $regex : new RegExp("Vi", "i") }})

For Case sensitive

db.users.find({"name":"Vi"})
// or
db.users.find({"email":"example@mail.com"})

search in user table

name is column name and "Vi" text that are searched

shapiro yaacov
  • 2,173
  • 2
  • 24
  • 34
Vikas Kumar
  • 102
  • 4
-1

Use $strcasecmp. The aggregation framework was introduced in MongoDB 2.2. You can use the string operator "$strcasecmp" to make a case-insensitive comparison between strings. It's more recommended and easier than using regex.

Here's the official document on the aggregation command operator: https://docs.mongodb.com/manual/reference/operator/aggregation/strcasecmp/#exp._S_strcasecmp .

Jogue Wasin
  • 67
  • 1
  • 4