How do I make case-insensitive queries on Mongodb

Question

var thename    Andrew   db collection find   name  thename      How do I query case insensitive  I want to find result even if  andrew

User · Accepted Answer

Chris Fulstow s solution will work   1   however  it may not be efficient  especially if your collection is very large  Non-rooted regular expressions  those not beginning with    which anchors the regular expression to the start of the string   and those using the i flag for case insensitivity will not use indexes  even if they exist   An alternative option you might consider is to denormalize your data to store a lower-case version of the name field  for instance as name lower  You can then query that efficiently  especially if it is indexed  for case-insensitive exact matches like   db collection find   name lower   thename toLowerCase       Or with a prefix match  a rooted regular expression  as   db collection find    name lower          regex  new RegExp       thename toLowerCase     i            Both of these queries will use an index on name lower

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

You d need to use a case-insensitive regular expression for this one  e g   db collection find     name       regex    Andrew i          To use the regex pattern from your thename variable  construct a new RegExp object   var thename    Andrew   db collection find     name       regex   new RegExp thename   i            Update  For exact match  you should use the regex  name     Andrew  i  Thanks to  Yannick L

User · Answer

The following query will find the documents with required string insensitively         and with global occurrence also  db collection find  name                                 regex  new RegExp thename   ig                                                    function err  doc                                               Your code here

User · Answer

I have solved it like this    var thename    Andrew    db collection find   name      regex   thename  options  i        If you want to query on  case-insensitive exact matchcing  then you can go like this   var thename      Andrew    db collection find   name      regex   thename  options  i

User · Answer

With Mongoose  and Node   this worked    User find   email    name company com  i    User find   email  new RegExp     emailVariable      i       In MongoDB  this worked    db users find   email     regex    name company com  i        Both lines are case-insensitive  The email in the DB could be NaMe CompanY Com and both lines will still find the object in the DB   Likewise  we could use   NaMe CompanY Com  i and it would still find email  name company com in the DB

User · Answer

To find case-insensitive literals string   Using regex  recommended   db collection find       name             regex  new RegExp       name replace   -                    g       amp            i               Using lower-case index  faster   db collection find       name lower  name toLowerCase         Regular expressions are slower than literal string matching  However  an additional lowercase field will increase your code complexity  When in doubt  use regular expressions  I would suggest to only use an explicitly lower-case field if it can replace your field  that is  you don t care about the case in the first place   Note that you will need to escape the name prior to regex  If you want user-input wildcards  prefer appending  replace    g        after escaping so that you can match  a   to find all names starting with  a

User · Answer

I just solved this problem a few hours ago   var thename    Andrew  db collection find    text     search  thename          Case sensitivity and diacritic sensitivity are set to false by default when doing queries this way    You can even expand upon this by selecting on the fields you need from Andrew s user object by doing it this way   db collection find    text     search  thename      select  age height weight      Reference  https   docs mongodb org manual reference operator query text  text

User · Answer

This will work perfectly db collection find   song Name      regex   searchParam   options   i       Just have to add in your regex  options   i  where i is case-insensitive

User · Answer

with mongoose on NodeJS that query   const countryName   req params country      country   new RegExp     countryName      i        or  const countryName   req params country      country      regex  new RegExp     countryName       options   i            australia    or  const countryName   req params country      country      regex  new RegExp     countryName      i             turkey    A full code example in Javascript  NodeJS with Mongoose ORM on MongoDB     get all customers that given country name app get   customers country  countryName    req  res    gt          res send  Got a GET request at  customer country   req params countryName          const countryName   req params countryName          using Regular Expression  case intensitive and equal    australia          const query      country   new RegExp     countryName      i             const query      country      regex  new RegExp     countryName      i            const query      country      regex  new RegExp     countryName       options   i            Customer find query  sort   name   asc              then customers   gt                res json customers                       catch error   gt                   error               res send error message

User · Answer

To find case Insensitive string use this   var thename    Andrew   db collection find   name    thename  i

User · Answer

MongoDB 3 4 now includes the ability to make a true case-insensitive index  which will dramtically increase the speed of case insensitive lookups on large datasets   It is made by specifying a collation with a strength of 2   Probably the easiest way to do it is to set a collation  on the database   Then all queries inherit that collation and will use it   db createCollection  cities     collation    locale   en US   strength  2       db names createIndex    city  1        inherits the default collation   You can also do it like this   db myCollection createIndex  city  1    collation   locale   en   strength  2       And use it like this   db myCollection find  city   new york    collation  locale   en   strength  2      This will return cities named  new york    New York    New york   etc   For more info  https   jira mongodb org browse SERVER-90

User · Answer

An easy way would be to use  toLower as below   db users aggregate                  project                name     toLower    name                                    match                name  the name to search

[regex] How do I make case-insensitive queries on Mongodb?

Examples related to regex

Examples related to mongodb

Examples related to database