Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MongoDB: Is it possible to make a case-insensitive query?

Example:

> db.stuff.save({"foo":"bar"});  > db.stuff.find({"foo":"bar"}).count(); 1 > db.stuff.find({"foo":"BAR"}).count(); 0 
like image 473
Luke Dennis Avatar asked Dec 07 '09 22:12

Luke Dennis


People also ask

How do I make case insensitive queries on MongoDB?

The aggregation framework was introduced in mongodb 2.2 . You can use the string operator "$strcasecmp" to make a case-insensitive comparison between strings. It's more recommended and easier than using regex.

Is MongoDB data case-sensitive?

As mentioned by @natac13 and @007_jb mongo shell is an interactive javascript interpreter and hence it is also case-sensitive.

How would you make a case insensitive query in my SQL?

Case insensitive SQL SELECT: Use upper or lower functions select * from users where lower(first_name) = 'fred'; As you can see, the pattern is to make the field you're searching into uppercase or lowercase, and then make your search string also be uppercase or lowercase to match the SQL function you've used.

Are MongoDB keys case-sensitive?

Mongodb supports case insensitive indexing now.


2 Answers

You could use a regex.

In your example that would be:

db.stuff.find( { foo: /^bar$/i } ); 

I must say, though, maybe you could just downcase (or upcase) the value on the way in rather than incurring the extra cost every time you find it. Obviously this wont work for people's names and such, but maybe use-cases like tags.

like image 187
rfunduk Avatar answered Sep 28 '22 05:09

rfunduk


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.

like image 29
Dan Avatar answered Sep 28 '22 03:09

Dan