MongoDB: Is it possible to make a case-insensitive

Question

Example:

> db.stuff.save({"foo":"bar"});

> db.stuff.find({"foo":"bar"}).count();
1
> db.stuff.find({"foo":"BAR"}).count();
0

Answer 1

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.

Answer 2

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

Answer 3

db.zipcodes.find({city : "NEW YORK"}); // Case-sensitive
db.zipcodes.find({city : /NEW york/i}); // Note the 'i' flag for case-insensitivity

Answer 4

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: "Oğuz" },
                            { name: "oğuz" },
                            { name: "OĞUZ" } ] )

// does not use index, finds one result
db.users.find( { name: "oğuz" } )

// uses the index, finds three results
db.users.find( { name: "oğuz" } ).collation( { locale: 'tr', strength: 2 } )

// does not use the index, finds three results (different strength)
db.users.find( { name: "oğuz" } ).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

Answer 5

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

Answer 6

db.company_profile.find({ "companyName" : { "$regex" : "Nilesh" , "$options" : "i"}});