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";

Answer 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.

Answer 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.