139 
var thename = 'Andrew';
db.collection.find({'name':thename});

How do I query case insensitive? I want to find result even if "andrew";

Improve this question
3
  • Possible duplicate of MongoDB: Is it possible to make a case-insensitive query? Commented Aug 7, 2017 at 21:54
  • 3
    A note to everyone who will attempt to use an answer involving regexes: Regexes need to be sanitized. Commented Dec 17, 2019 at 10:44
  • @sean, can you add an answer using sanitized regex, I'll appreciate it. Commented Sep 4, 2023 at 17:52

15 Answers 15

Reset to default
161

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

Great answer, my regex approach really slows down once it has to scan a few million docs.
This is actually not fully correct, because you might find "Andrew something" while looking for "Andrew". So adjust the regex to: new RegExp('^'+ username + '$', "i") to be an exact match.
According to MongoDB website any case insensitive regex is not index efficient "$regex can only use an index efficiently when the regular expression has an anchor for the beginning (i.e. ^) of a string and is a case-sensitive match"
With Mongoose this worked for me : User.find({'username': {$regex: new RegExp('^' + username.toLowerCase(), 'i')}}, function(err, res){ if(err) throw err; next(null, res); });
Never forget to escape the name when working with regular expressions. We do not want injections to take over the beauty of mongodb. Just imagine you used this code for a login page and the username was ".*".
|
145

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.

Improve this answer

6 Comments

Do you know how to do this using Node.js mongoose ?
I wonder how well this will work with large collections. You'd loose the benefit of a sort functinon
This is wrong, it will match any document containing "andrew" for name, not just equaling.
@JonathanCremin to help people you should post the correct answer: { "name": /^Andrew$/i }
@YannickL. 1+ for doing the common sense thing. I was just passing by not what i was looking for.
|
69

I have solved it like this.

 var thename = 'Andrew';
 db.collection.find({'name': {'$regex': thename,$options:'i'}});

If you want to query for case-insensitive and exact, then you can go like this.

var thename =  '^Andrew$';
db.collection.find({'name': {'$regex': thename,$options:'i'}});
Improve this answer

1 Comment

This was the only solution worked for me. ( Node.js, "mongoose": "^7.2.0" )
14

  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:

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.

Improve this answer

1 Comment

Thank you Raymond. It worked User.find({ email: new RegExp(^${emailVariable}$, 'i') }) example
13

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

Improve this answer

1 Comment

strength: 1 is sufficient for case-insensitive, diacritic-insensitive indexing. docs.mongodb.com/manual/reference/collation
9

... 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) => {
    //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 => {
            res.json(customers);
        })
        .catch(error => {
            // error..
            res.send(error.message);
        });
});
Improve this answer

2 Comments

Thanks, what is the difference between the first method and the third method?
Be caution! This approach don't use indexes. It may cause slow queries on large databases.
6

To find case Insensitive string use this,

var thename = "Andrew";
db.collection.find({"name":/^thename$/i})
Improve this answer

1 Comment

Why are you adding a duplicate answer as it is already there in stackoverflow.com/a/7101868/4273915
5

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

Improve this answer

1 Comment

$text performs a text search on the content of the fields indexed with a text index.
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
Improve this answer

Comments

4

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.

Improve this answer

Comments

3

To find case-insensitive literals string:

Using regex (recommended)

db.collection.find({
    name: {
        $regex: new RegExp('^' + name.replace(/[-\/\\^$*+?.()|[\]{}]/g, '\\$&') + '$', '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'.

Improve this answer

Comments

2

Regex queries will be slower than index based queries.

You can create an index with specific collation as below
db.collection.createIndex({field:1},{collation: {locale:'en',strength:2}},{background : true});

The above query will create an index that ignores the case of the string. The collation needs to be specified with each query so it uses the case insensitive index.

Query
db.collection.find({field:'value'}).collation({locale:'en',strength:2});

Note - if you don't specify the collation with each query, query will not use the new index.

Refer to the mongodb doc here for more info - https://docs.mongodb.com/manual/core/index-case-insensitive/

Improve this answer

Comments

1

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...
                  });
Improve this answer

Comments

0

in mongoose Schema in the type definition of the field you want to search let say email just add property lowercase:true

Improve this answer

Comments

-3

An easy way would be to use $toLower as below.

db.users.aggregate([
    {
        $project: {
            name: { $toLower: "$name" }
        }
    },
    {
        $match: {
            name: the_name_to_search
        }
    }
])
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.