How do I make case-insensitive queries on Mongodb?

Solution 1:

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.

Solution 2:

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.

Solution 3:

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'}});

Solution 4:

1. With Mongoose (and Node), this worked:

   • User.find({ email: /^[email protected]$/i })

   • User.find({ email: new RegExp(`^${emailVariable}$`, 'i') })

2. In MongoDB, this worked:

   • db.users.find({ email: { $regex: /^[email protected]$/i }})

Both lines are case-insensitive. The email in the DB could be [email protected] and both lines will still find the object in the DB.

Likewise, we could use /^[email protected]$/i and it would still find email: [email protected] in the DB.

Solution 5:

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