MongoDB Is it possible to make a case-insensitive query

Question

Example    gt  db stuff save   foo   bar       gt  db stuff find   foo   bar    count    1  gt  db stuff find   foo   BAR    count    0

User · Answer

db company profile find    companyName        regex     Nilesh      options     i

User · Answer

I ve created a simple Func for the case insensitive regex  which I use in my filter   private Func lt string  BsonRegularExpression gt  CaseInsensitiveCompare    field    gt               BsonRegularExpression Create new Regex field  RegexOptions IgnoreCase      Then you simply filter on a field as follows   db stuff find   foo   CaseInsensitiveCompare  bar     count

User · Answer

For any one using Golang and wishes to have case sensitive full text search with mongodb and the mgo godoc globalsign library   collation     amp mgo Collation      Locale     en       Strength  2       err    collection Find query  Collation collation

User · Answer

The best method is in your language of choice  when creating a model wrapper for your objects  have your save   method iterate through a set of fields that you will be searching on that are also indexed  those set of fields should have lowercase counterparts that are then used for searching   Every time the object is saved again  the lowercase properties are then checked and updated with any changes to the main properties  This will make it so you can search efficiently  but hide the extra work needed to update the lc fields each time   The lower case fields could be a key value object store or just the field name with a prefixed lc   I use the second one to simplify querying  deep object querying can be confusing at times     Note  you want to index the lc  fields  not the main fields they are based off of

User · Answer

Mongo  current version 2 0 0  doesn t allow case-insensitive searches against indexed fields - see their documentation   For non-indexed fields  the regexes listed in the other answers should be fine

User · Answer

You can use Case Insensitive Indexes   The following example creates a collection with no default collation  then adds an index on the name field with a case insensitive collation  International Components for Unicode     strength  CollationStrength Secondary   Secondary level of comparison  Collation performs comparisons up to secondary   differences  such as diacritics  That is  collation performs comparisons of    base characters  primary differences  and diacritics  secondary differences     Differences between base characters takes precedence over secondary    differences     db users createIndex    name  1    collation    locale   tr   strength  2         To use the index  queries must specify the same collation   db users insert      name   Oguz                                   name   oguz                                   name   OGUZ            does not use index  finds one result db users find    name   oguz          uses the index  finds three results db users find    name   oguz      collation    locale   tr   strength  2         does not use the index  finds three results  different strength  db users find    name   oguz      collation    locale   tr   strength  1       or you can create a collection with default collation   db createCollection  users     collation    locale   tr   strength  2       db users createIndex    name   1        inherits the default collation

User · Answer

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

User · Answer

I m surprised nobody has warned about the risk of regex injection by using   bar  i if bar is a password or an account id search   I e   bar   gt     myhackeddomain com e g   so here comes my bet  use  Q  E regex special chars  provided in PERL db stuff find    foo     Qbar E  i       You should escape bar variable   chars with    to avoid  E exploit again when e g  bar     E   myhackeddomain com Q  Another option is to use a regex escape char strategy like the one described here Javascript equivalent of Perl  39 s  Q      E or quotemeta

User · Answer

I had faced a similar issue and this is what worked for me     const flavorExists   await Flavors findOne        flavor name      regex  flavorName   options   i

User · Answer

One very important thing to keep in mind when using a Regex based query - When you are doing this for a login system  escape every single character you are searching for  and don t forget the   and   operators  Lodash has a nice function for this  should you be using it already   db stuff find   regex  new RegExp   escapeRegExp bar    options   i      Why  Imagine a user entering    as his username  That would match all usernames  enabling a login by just guessing any user s password

User · Answer

Keep in mind that the previous example   db stuff find    foo   bar i        will cause every entries containing bar to match the query   bar1  barxyz  openbar    it could be very dangerous for a username search on a auth function      You may need to make it match only the search term by using the appropriate regexp syntax as   db stuff find    foo    bar  i        See http   www regular-expressions info  for syntax help on regular expressions

User · Answer

UPDATE   The original answer is now obsolete  Mongodb now supports advanced full text searching  with many features   ORIGINAL ANSWER   It should be noted that searching with regex s case insensitive  i means that mongodb cannot search by index  so queries against large datasets can take a long time     Even with small datasets  it s not very efficient   You take a far bigger cpu hit than your query warrants  which could become an issue if you are trying to achieve scale   As an alternative  you can store an uppercase copy and search against that   For instance  I have a User table that has a username which is mixed case  but the id is an uppercase copy of the username   This ensures case-sensitive duplication is impossible  having both  Foo  and  foo  will not be allowed   and I can search by id   username toUpperCase   to get a case-insensitive search for username   If your field is large  such as a message body  duplicating data is probably not a good option   I believe using an extraneous indexer like Apache Lucene is the best option in that case

User · Answer

You could use a regex   In your example that would be   db stuff find    foo    bar  i        I must say  though  maybe you could just downcase  or upcase  the value on the way in rather than incurring the extra cost every time you find it  Obviously this wont work for people s names and such  but maybe use-cases like tags

User · Answer

Using a filter works for me in C    string s    searchTerm       var filter   Builders lt Model gt  Filter Where p   gt  p Title ToLower   Contains s ToLower                      var listSorted   collection Find filter  ToList                    var list   collection Find filter  ToList      It may even use the index because I believe the methods are called after the return happens but I haven t tested this out yet   This also avoids a problem of   var filter   Builders lt Model gt  Filter Eq p   gt  p Title ToLower    s ToLower       that mongodb will think p Title ToLower   is a property and won t map properly

User · Answer

db zipcodes find  city    NEW YORK        Case-sensitive db zipcodes find  city    NEW york i       Note the  i  flag for case-insensitivity

User · Answer

Suppose you want to search  column  in  Table  and you want case insenstive search  The best and efficient way is as below     create empty JSON Object mycolumn          check if column has valid value if column        mycolumn column     regex  new RegExp column    options   i      Table find mycolumn     Above code just adds your search value as RegEx and searches in with insensitve criteria set with  i  as option   All the best

User · Answer

If you need to create the regexp from a variable  this is a much better way to do it  https   stackoverflow com a 10728069 309514  You can then do something like   var string    SomeStringToFind   var regex   new RegExp       string       join       i       Creates a regex of    SomeStringToFind  i db stuff find    foo  regex        This has the benefit be being more programmatic or you can get a performance boost by compiling it ahead of time if you re reusing it a lot

User · Answer

TL DR Correct way to do this in mongo Do not Use RegExp Go natural And use mongodb s inbuilt indexing   search Step 1   db articles insert               id  1  subject   quot coffee quot   author   quot xyz quot   views  50            id  2  subject   quot Coffee Shopping quot   author   quot efg quot   views  5            id  3  subject   quot Baking a cake quot   author   quot abc quot   views  90             id  4  subject   quot baking quot   author   quot xyz quot   views  100            id  5  subject   quot Caf   Con Leche quot   author   quot abc quot   views  200            id  6  subject   quot         quot   author   quot jkl quot   views  80            id  7  subject   quot coffee and cream quot   author   quot efg quot   views  10            id  8  subject   quot Cafe con Leche quot   author   quot xyz quot   views  10             Step 2   Need to create index on whichever TEXT field you want to search   without indexing query will be extremely slow db articles createIndex    subject   quot text quot       step 3   db articles find     text     search   quot coffee quot   caseSensitive  true          FOR SENSITIVITY db articles find     text     search   quot coffee quot   caseSensitive  false         FOR INSENSITIVITY

User · Answer

For searching a variable and escaping it   const escapeStringRegexp   require  escape-string-regexp   const name    foo  db stuff find  name  new RegExp       escapeStringRegexp name          i          Escaping the variable protects the query against attacks with      or other regex    escape-string-regexp

User · Answer

Use RegExp   In case if any other options do not work for you  RegExp is a good option  It makes the string case insensitive   var username   new RegExp        John          i       use username in queries  and then its done   I hope it will work for you too  All the Best

User · Answer

Starting with MongoDB 3 4  the recommended way to perform fast case-insensitive searches is to use a Case Insensitive Index  I personally emailed one of the founders to please get this working  and he made it happen  It was an issue on JIRA since 2009  and many have requested the feature  Here s how it works  A case-insensitive index is made by specifying a collation with a strength of either 1 or 2   You can create a case-insensitive index like this  db cities createIndex      city  1             collation          locale   en         strength  2               You can also specify a default collation per collection when you create them  db createCollection  cities     collation    locale   en   strength  2         In either case  in order to use the case-insensitive index  you need to specify the same collation in the find operation that was used when creating the index or the collection  db cities find      city   new york      collation      locale   en   strength  2       This will return  quot New York quot    quot new york quot    quot New york quot  etc  Other notes  The answers suggesting to use full-text search are wrong in this case  and potentially dangerous   The question was about making a case-insensitive query  e g  username   bill  matching BILL or Bill  not a full-text search query  which would also match stemmed words of bill  such as Bills  billed etc   The answers suggesting to use regular expressions are slow  because even with indexes  the documentation states    quot Case insensitive regular expression queries generally cannot use indexes effectively  The  regex implementation is not collation-aware and is unable to utilize case-insensitive indexes  quot    regex answers also run the risk of user input injection

User · Answer

These have been tested for string searches     id      CM                     find  id where  id contains   - gt CM    id     CM                      find  id where  id starts     - gt CM    id    CM                       find  id where  id ends       - gt CM     id      UcM075237   i          find  id where  id contains   - gt UcM075237  ignore upper lower case    id     UcM075237 i             find  id where  id starts     - gt UcM075237  ignore upper lower case    id    UcM075237  i             find  id where  id ends       - gt UcM075237  ignore upper lower case

User · Answer

Using Mongoose this worked for me   var find   function username  next       User find   username     regex  new RegExp       username   i      function err  res           if err  throw err          next null  res

User · Answer

As you can see in mongo docs - since version 3 2  text index is case-insensitive by default  https   docs mongodb com manual core index-text  text-index-case-insensitivity    Create a text index and use  text operator in your query

[mongodb] MongoDB: Is it possible to make a case-insensitive query?

Examples related to mongodb

Examples related to case-insensitive