MongoDB: Is it possible to make a case-insensitive query?
up vote
244
down vote
favorite
Example:
> db.stuff.save({"foo":"bar"});
> db.stuff.find({"foo":"bar"}).count();
1
> db.stuff.find({"foo":"BAR"}).count();
0
mongodb case-insensitive
add a comment |
up vote
244
down vote
favorite
Example:
> db.stuff.save({"foo":"bar"});
> db.stuff.find({"foo":"bar"}).count();
1
> db.stuff.find({"foo":"BAR"}).count();
0
mongodb case-insensitive
3
Since MongoDB 3.2 you can execute case-insensitive search with$caseSensitive: false
. See: docs.mongodb.org/manual/reference/operator/query/text/…
– martin
Apr 4 '16 at 8:21
4
Note that that is on text indexes only.
– Willem D'Haeseleer
Aug 16 '16 at 15:40
add a comment |
up vote
244
down vote
favorite
up vote
244
down vote
favorite
Example:
> db.stuff.save({"foo":"bar"});
> db.stuff.find({"foo":"bar"}).count();
1
> db.stuff.find({"foo":"BAR"}).count();
0
mongodb case-insensitive
Example:
> db.stuff.save({"foo":"bar"});
> db.stuff.find({"foo":"bar"}).count();
1
> db.stuff.find({"foo":"BAR"}).count();
0
mongodb case-insensitive
mongodb case-insensitive
edited Dec 8 '09 at 5:18
John Saunders
147k22204359
147k22204359
asked Dec 7 '09 at 22:36
Luke Dennis
6,912154864
6,912154864
3
Since MongoDB 3.2 you can execute case-insensitive search with$caseSensitive: false
. See: docs.mongodb.org/manual/reference/operator/query/text/…
– martin
Apr 4 '16 at 8:21
4
Note that that is on text indexes only.
– Willem D'Haeseleer
Aug 16 '16 at 15:40
add a comment |
3
Since MongoDB 3.2 you can execute case-insensitive search with$caseSensitive: false
. See: docs.mongodb.org/manual/reference/operator/query/text/…
– martin
Apr 4 '16 at 8:21
4
Note that that is on text indexes only.
– Willem D'Haeseleer
Aug 16 '16 at 15:40
3
3
Since MongoDB 3.2 you can execute case-insensitive search with
$caseSensitive: false
. See: docs.mongodb.org/manual/reference/operator/query/text/…– martin
Apr 4 '16 at 8:21
Since MongoDB 3.2 you can execute case-insensitive search with
$caseSensitive: false
. See: docs.mongodb.org/manual/reference/operator/query/text/…– martin
Apr 4 '16 at 8:21
4
4
Note that that is on text indexes only.
– Willem D'Haeseleer
Aug 16 '16 at 15:40
Note that that is on text indexes only.
– Willem D'Haeseleer
Aug 16 '16 at 15:40
add a comment |
22 Answers
22
active
oldest
votes
up vote
273
down vote
accepted
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.
25
This works perfectly. Got it working in PHP with: $collection->find(array('key' => new MongoRegex('/'.$val.'/i')));
– Luke Dennis
Dec 9 '09 at 4:22
2
Especially if you're interpolating a string ({foo: /#{x}/i}) that could have a question mark in it..
– Peter Ehrlich
Dec 16 '11 at 18:53
13
Dont forget also ^and $ : MongoRegex('/^' . preg_quote($val) . '$/i')
– Julien
Jan 1 '13 at 20:26
13
Note that this will do a fullscan instead of using index.
– Martin Konicek
Apr 25 '13 at 13:29
12
it won't do a fullscan if he uses the ^ anchor at the beginning, hence the importance of Julien's advice.
– Pax
Jul 6 '13 at 22:37
|
show 10 more comments
up vote
189
down vote
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.
Any documentation available that show how the indexes work? I'm asking because, if I recall, marklogic is able to hold an additional case insensitive index... maybe mongo does the same thing?
– RayLoveless
Dec 31 '12 at 17:05
Raymo, a case insensitive index feature does not exist today in Mongo, but it's being talked about. jira.mongodb.org/browse/SERVER-90
– Dan
Feb 19 '13 at 3:18
22
FYI, my answer is now obsolete. Mongodb now supports advanced full text searching, with many features. See docs.mongodb.org/manual/core/index-text
– Dan
Aug 11 '14 at 20:02
1
@Dan, just for the info, in latest MongoDB, "If an index exists for the field, then MongoDB matches the regular expression against the values in the index, which can be faster than a collection scan." -- docs.mongodb.org/manual/reference/operator/query/regex/…
– Sergiy Sokolenko
Nov 16 '15 at 13:28
@Dan, it should be noted, that the fancy new full text index has it's problems - "For the Latin alphabet, text indexes are case insensitive for non-diacritics; i.e. case insensitive for [A-z]. For all other characters, text indexes treat them as distinct."; So, for non Latin alphabet, it might be reasonable to use regex search which should also take an advantage of existing index (see my comment above).
– Sergiy Sokolenko
Nov 16 '15 at 13:32
|
show 4 more comments
up vote
58
down vote
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
add a comment |
up vote
54
down vote
If you need to create the regexp from a variable, this is a much better way to do it: https://stackoverflow.com/a/10728069/309514
You can then do something like:
var string = "SomeStringToFind";
var regex = new RegExp(["^", string, "$"].join(""), "i");
// Creates a regex of: /^SomeStringToFind$/i
db.stuff.find( { foo: regex } );
This has the benefit be being more programmatic or you can get a performance boost by compiling it ahead of time if you're reusing it a lot.
new RegExp("^" + req.params.term.toLowerCase(), "i")
also works fine
– Tahir Yasin
Mar 29 '17 at 15:43
you should consider escaping the string for increasing security if the variable comes from a request: stackoverflow.com/a/50633536/5195127
– davidivad
May 31 at 22:19
add a comment |
up vote
23
down vote
As of Mongodb 3.4 you should use a case-insensitive collation index. This is the fastest way to do a case-insensitive search over datasets of increasingly large size. I personally emailed one of the founders to please get this working, and he made it happen! (It was an issue on JIRA for like 5 years, and many have requested the feature). Here's how it works:
A case-insensitive index is made by specifying a collation with a strength of either 1 or 2. You can create a case-insensitive index like this:
db.myCollection.createIndex({city: 1}, {collation: {locale: "en", strength: 2}});
Or you can do it for the whole collection by default when you create the database like so:
db.createCollection("Cities",{collation: {locale: "en",strength:2}});
And use it like this:
db.myCollection.find({city: "new york"}).collation({locale: "en", strength: 2});
This will return "New York", "new york", etc.
Alternatively, you can make all indexes use a collation by default when you make the collection like this:
db.createCollection("cities",{collation:{locale: "en", strength: 2}});
The benefit to this method is much improved efficiency and speed on larger datasets.
For more info: https://jira.mongodb.org/browse/SERVER-90 , https://docs.mongodb.com/manual/reference/collation/
An important point is that "To use the index, queries must specify the same collation", explicitly: docs.mongodb.com/manual/core/index-case-insensitive/…
– dskrvk
Sep 7 at 19:06
add a comment |
up vote
16
down vote
db.zipcodes.find({city : "NEW YORK"}); // Case-sensitive
db.zipcodes.find({city : /NEW york/i}); // Note the 'i' flag for case-insensitivity
1
Please add description to code.
– Parth Trivedi
Dec 17 '15 at 13:02
6
@ParthTrivedi, comments are about as long as the code itself. Do you want 3 pages essay or something?
– Oleg V. Volkov
Dec 17 '15 at 14:22
@OlegV.Volkov must have description about how your answer appropriate and what is wrong in questioner code.
– Parth Trivedi
Dec 18 '15 at 4:39
add a comment |
up vote
12
down vote
TL;DR
Correct way to do this in mongo
Do not Use RegExp
Go natural And use mongodb's inbuilt indexing , search
Step 1 :
db.articles.insert(
[
{ _id: 1, subject: "coffee", author: "xyz", views: 50 },
{ _id: 2, subject: "Coffee Shopping", author: "efg", views: 5 },
{ _id: 3, subject: "Baking a cake", author: "abc", views: 90 },
{ _id: 4, subject: "baking", author: "xyz", views: 100 },
{ _id: 5, subject: "Café Con Leche", author: "abc", views: 200 },
{ _id: 6, subject: "Сырники", author: "jkl", views: 80 },
{ _id: 7, subject: "coffee and cream", author: "efg", views: 10 },
{ _id: 8, subject: "Cafe con Leche", author: "xyz", views: 10 }
]
)
Step 2 :
Need to create index on whichever TEXT field you want to search , without indexing query will be extremely slow
db.articles.createIndex( { subject: "text" } )
step 3 :
db.articles.find( { $text: { $search: "coffee",$caseSensitive :true } } ) //FOR SENSITIVITY
db.articles.find( { $text: { $search: "coffee",$caseSensitive :false } } ) //FOR INSENSITIVITY
1
Good option, but there's nothing more "correct" about using a text index versus a regex, it's just another option. It's overkill for the OP's case.
– JohnnyHK
Aug 28 '16 at 2:16
1
Except regex is significantly slower. Fulltext searching is also slow, but not as slow. The fastest (but more bloated) way would be a separate field which is always set to lowercase.
– Tom Mettam
Jan 17 at 0:37
add a comment |
up vote
9
down vote
Mongo (current version 2.0.0) doesn't allow case-insensitive searches against indexed fields - see their documentation. For non-indexed fields, the regexes listed in the other answers should be fine.
18
Just to clarify this: case-insensitive searches are allowed on indexed fields, they just won't use the index and will be as slow as if the field wasn't indexed.
– heavi5ide
Dec 19 '11 at 17:22
@heavi5ide since this question is being used to mark duplicates I thought I would clarify that regexes (needed for case insensitive searches) do use the index, however, they must do a full index scan. In other words they cannot efficiently use the index. Fortunately the documentation has since been updated from 2011 but still good to note here too.
– Sammaye
Aug 13 '14 at 17:37
add a comment |
up vote
7
down vote
Using Mongoose this worked for me:
var find = function(username, next){
User.find({'username': {$regex: new RegExp('^' + username, 'i')}}, function(err, res){
if(err) throw err;
next(null, res);
});
}
8
Isn't the.toLowerCase()
redundant if you're specifying the case-insensitive flag ofi
?
– k00k
Jul 1 '15 at 15:01
Yes it is. You don't need .toLowerCase(). I have removed it from the answer.
– ChrisRich
May 18 '17 at 1:00
hmm should this work like that? When I search for "mark" it also gets every record with "marko" - is there a way only ignore case-sensitivity?
– Suisse
Jun 19 '17 at 0:54
Ok found it, the correct regex would be: '^' + serach_name + '$', "i"
– Suisse
Jun 19 '17 at 1:02
1
This is DANGEROUS. You are not escaping username, so any arbitrary regex can be injected.
– Tom Mettam
Jan 17 at 0:37
add a comment |
up vote
5
down vote
The best method is in your language of choice, when creating a model wrapper for your objects, have your save() method iterate through a set of fields that you will be searching on that are also indexed; those set of fields should have lowercase counterparts that are then used for searching.
Every time the object is saved again, the lowercase properties are then checked and updated with any changes to the main properties. This will make it so you can search efficiently, but hide the extra work needed to update the lc fields each time.
The lower case fields could be a key:value object store or just the field name with a prefixed lc_. I use the second one to simplify querying (deep object querying can be confusing at times).
Note: you want to index the lc_ fields, not the main fields they are based off of.
add a comment |
up vote
5
down vote
Suppose you want to search "column" in "Table" and you want case insenstive search. The best and efficient way is as below;
//create empty JSON Object
mycolumn = {};
//check if column has valid value
if(column) {
mycolumn.column = {$regex: new RegExp(column), $options: "i"};
}
Table.find(mycolumn);
Above code just adds your search value as RegEx and searches in with insensitve criteria set with "i" as option.
All the best.
add a comment |
up vote
5
down vote
One very important thing to keep in mind when using a Regex based query - When you are doing this for a login system, escape every single character you are searching for, and don't forget the ^ and $ operators. Lodash has a nice function for this, should you be using it already:
db.stuff.find({$regex: new RegExp(_.escapeRegExp(bar), $options: 'i'})
Why? Imagine a user entering .*
as his username. That would match all usernames, enabling a login by just guessing any user's password.
add a comment |
up vote
4
down vote
db.company_profile.find({ "companyName" : { "$regex" : "Nilesh" , "$options" : "i"}});
add a comment |
up vote
3
down vote
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.
Here's the official document on the aggregation command operator: https://docs.mongodb.com/manual/reference/operator/aggregation/strcasecmp/#exp._S_strcasecmp .
2
how to use this in a find() query? db.stuff.find({ name: $strcasecmp(name)}) ?
– Suisse
Jun 19 '17 at 0:51
add a comment |
up vote
1
down vote
Using a filter works for me in C#.
string s = "searchTerm";
var filter = Builders<Model>.Filter.Where(p => p.Title.ToLower().Contains(s.ToLower()));
var listSorted = collection.Find(filter).ToList();
var list = collection.Find(filter).ToList();
It may even use the index because I believe the methods are called after the return happens but I haven't tested this out yet.
This also avoids a problem of
var filter = Builders<Model>.Filter.Eq(p => p.Title.ToLower(), s.ToLower());
that mongodb will think p.Title.ToLower() is a property and won't map properly.
Thanks, It works for Me. Here we need to obtain filter in variable then pass in Find() method.
– Nilay
Aug 25 at 11:33
add a comment |
up vote
1
down vote
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
There seems minor syntax issue(missing Braces).Please update the query:db.users.createIndex( { name: 1 }, {collation: { locale: 'tr', strength: 2 } } )
– Mohd Belal
Apr 12 at 11:56
add a comment |
up vote
1
down vote
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
add a comment |
up vote
0
down vote
I've created a simple Func for the case insensitive regex, which I use in my filter.
private Func<string, BsonRegularExpression> CaseInsensitiveCompare = (field) =>
BsonRegularExpression.Create(new Regex(field, RegexOptions.IgnoreCase));
Then you simply filter on a field as follows.
db.stuff.find({"foo": CaseInsensitiveCompare("bar")}).count();
add a comment |
up vote
0
down vote
As you can see in mongo docs - since version 3.2 $text
index is case-insensitive by default: https://docs.mongodb.com/manual/core/index-text/#text-index-case-insensitivity
Create a text index and use $text operator in your query.
add a comment |
up vote
0
down vote
These have been tested for string searches
{'_id': /.*CM.*/} ||find _id where _id contains ->CM
{'_id': /^CM/} ||find _id where _id starts ->CM
{'_id': /CM$/} ||find _id where _id ends ->CM
{'_id': /.*UcM075237.*/i} ||find _id where _id contains ->UcM075237, ignore upper/lower case
{'_id': /^UcM075237/i} ||find _id where _id starts ->UcM075237, ignore upper/lower case
{'_id': /UcM075237$/i} ||find _id where _id ends ->UcM075237, ignore upper/lower case
add a comment |
up vote
0
down vote
I had faced a similar issue and this is what worked for me:
const flavorExists = await Flavors.findOne({
'flavor.name': { $regex: flavorName, $options: 'i' },
});
add a comment |
up vote
0
down vote
Use RegExp,
In case if any other options do not work for you, RegExp is a good option. It makes the string case sensitive.
var username = "John";
var uname = new RegExp(username, "i");
The value of uname
will be like /John/i
.
use uname in queries instead of username,
and then its done.
I hope it will work for you too. All the Best.
add a comment |
22 Answers
22
active
oldest
votes
22 Answers
22
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
273
down vote
accepted
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.
25
This works perfectly. Got it working in PHP with: $collection->find(array('key' => new MongoRegex('/'.$val.'/i')));
– Luke Dennis
Dec 9 '09 at 4:22
2
Especially if you're interpolating a string ({foo: /#{x}/i}) that could have a question mark in it..
– Peter Ehrlich
Dec 16 '11 at 18:53
13
Dont forget also ^and $ : MongoRegex('/^' . preg_quote($val) . '$/i')
– Julien
Jan 1 '13 at 20:26
13
Note that this will do a fullscan instead of using index.
– Martin Konicek
Apr 25 '13 at 13:29
12
it won't do a fullscan if he uses the ^ anchor at the beginning, hence the importance of Julien's advice.
– Pax
Jul 6 '13 at 22:37
|
show 10 more comments
up vote
273
down vote
accepted
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.
25
This works perfectly. Got it working in PHP with: $collection->find(array('key' => new MongoRegex('/'.$val.'/i')));
– Luke Dennis
Dec 9 '09 at 4:22
2
Especially if you're interpolating a string ({foo: /#{x}/i}) that could have a question mark in it..
– Peter Ehrlich
Dec 16 '11 at 18:53
13
Dont forget also ^and $ : MongoRegex('/^' . preg_quote($val) . '$/i')
– Julien
Jan 1 '13 at 20:26
13
Note that this will do a fullscan instead of using index.
– Martin Konicek
Apr 25 '13 at 13:29
12
it won't do a fullscan if he uses the ^ anchor at the beginning, hence the importance of Julien's advice.
– Pax
Jul 6 '13 at 22:37
|
show 10 more comments
up vote
273
down vote
accepted
up vote
273
down vote
accepted
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.
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.
edited Feb 26 '17 at 14:23
answered Dec 7 '09 at 22:46
rfunduk
25.4k45349
25.4k45349
25
This works perfectly. Got it working in PHP with: $collection->find(array('key' => new MongoRegex('/'.$val.'/i')));
– Luke Dennis
Dec 9 '09 at 4:22
2
Especially if you're interpolating a string ({foo: /#{x}/i}) that could have a question mark in it..
– Peter Ehrlich
Dec 16 '11 at 18:53
13
Dont forget also ^and $ : MongoRegex('/^' . preg_quote($val) . '$/i')
– Julien
Jan 1 '13 at 20:26
13
Note that this will do a fullscan instead of using index.
– Martin Konicek
Apr 25 '13 at 13:29
12
it won't do a fullscan if he uses the ^ anchor at the beginning, hence the importance of Julien's advice.
– Pax
Jul 6 '13 at 22:37
|
show 10 more comments
25
This works perfectly. Got it working in PHP with: $collection->find(array('key' => new MongoRegex('/'.$val.'/i')));
– Luke Dennis
Dec 9 '09 at 4:22
2
Especially if you're interpolating a string ({foo: /#{x}/i}) that could have a question mark in it..
– Peter Ehrlich
Dec 16 '11 at 18:53
13
Dont forget also ^and $ : MongoRegex('/^' . preg_quote($val) . '$/i')
– Julien
Jan 1 '13 at 20:26
13
Note that this will do a fullscan instead of using index.
– Martin Konicek
Apr 25 '13 at 13:29
12
it won't do a fullscan if he uses the ^ anchor at the beginning, hence the importance of Julien's advice.
– Pax
Jul 6 '13 at 22:37
25
25
This works perfectly. Got it working in PHP with: $collection->find(array('key' => new MongoRegex('/'.$val.'/i')));
– Luke Dennis
Dec 9 '09 at 4:22
This works perfectly. Got it working in PHP with: $collection->find(array('key' => new MongoRegex('/'.$val.'/i')));
– Luke Dennis
Dec 9 '09 at 4:22
2
2
Especially if you're interpolating a string ({foo: /#{x}/i}) that could have a question mark in it..
– Peter Ehrlich
Dec 16 '11 at 18:53
Especially if you're interpolating a string ({foo: /#{x}/i}) that could have a question mark in it..
– Peter Ehrlich
Dec 16 '11 at 18:53
13
13
Dont forget also ^and $ : MongoRegex('/^' . preg_quote($val) . '$/i')
– Julien
Jan 1 '13 at 20:26
Dont forget also ^and $ : MongoRegex('/^' . preg_quote($val) . '$/i')
– Julien
Jan 1 '13 at 20:26
13
13
Note that this will do a fullscan instead of using index.
– Martin Konicek
Apr 25 '13 at 13:29
Note that this will do a fullscan instead of using index.
– Martin Konicek
Apr 25 '13 at 13:29
12
12
it won't do a fullscan if he uses the ^ anchor at the beginning, hence the importance of Julien's advice.
– Pax
Jul 6 '13 at 22:37
it won't do a fullscan if he uses the ^ anchor at the beginning, hence the importance of Julien's advice.
– Pax
Jul 6 '13 at 22:37
|
show 10 more comments
up vote
189
down vote
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.
Any documentation available that show how the indexes work? I'm asking because, if I recall, marklogic is able to hold an additional case insensitive index... maybe mongo does the same thing?
– RayLoveless
Dec 31 '12 at 17:05
Raymo, a case insensitive index feature does not exist today in Mongo, but it's being talked about. jira.mongodb.org/browse/SERVER-90
– Dan
Feb 19 '13 at 3:18
22
FYI, my answer is now obsolete. Mongodb now supports advanced full text searching, with many features. See docs.mongodb.org/manual/core/index-text
– Dan
Aug 11 '14 at 20:02
1
@Dan, just for the info, in latest MongoDB, "If an index exists for the field, then MongoDB matches the regular expression against the values in the index, which can be faster than a collection scan." -- docs.mongodb.org/manual/reference/operator/query/regex/…
– Sergiy Sokolenko
Nov 16 '15 at 13:28
@Dan, it should be noted, that the fancy new full text index has it's problems - "For the Latin alphabet, text indexes are case insensitive for non-diacritics; i.e. case insensitive for [A-z]. For all other characters, text indexes treat them as distinct."; So, for non Latin alphabet, it might be reasonable to use regex search which should also take an advantage of existing index (see my comment above).
– Sergiy Sokolenko
Nov 16 '15 at 13:32
|
show 4 more comments
up vote
189
down vote
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.
Any documentation available that show how the indexes work? I'm asking because, if I recall, marklogic is able to hold an additional case insensitive index... maybe mongo does the same thing?
– RayLoveless
Dec 31 '12 at 17:05
Raymo, a case insensitive index feature does not exist today in Mongo, but it's being talked about. jira.mongodb.org/browse/SERVER-90
– Dan
Feb 19 '13 at 3:18
22
FYI, my answer is now obsolete. Mongodb now supports advanced full text searching, with many features. See docs.mongodb.org/manual/core/index-text
– Dan
Aug 11 '14 at 20:02
1
@Dan, just for the info, in latest MongoDB, "If an index exists for the field, then MongoDB matches the regular expression against the values in the index, which can be faster than a collection scan." -- docs.mongodb.org/manual/reference/operator/query/regex/…
– Sergiy Sokolenko
Nov 16 '15 at 13:28
@Dan, it should be noted, that the fancy new full text index has it's problems - "For the Latin alphabet, text indexes are case insensitive for non-diacritics; i.e. case insensitive for [A-z]. For all other characters, text indexes treat them as distinct."; So, for non Latin alphabet, it might be reasonable to use regex search which should also take an advantage of existing index (see my comment above).
– Sergiy Sokolenko
Nov 16 '15 at 13:32
|
show 4 more comments
up vote
189
down vote
up vote
189
down vote
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.
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.
edited Sep 20 '16 at 19:37
Jhuliano Moreno
669420
669420
answered Dec 14 '10 at 16:22
Dan
2,4271126
2,4271126
Any documentation available that show how the indexes work? I'm asking because, if I recall, marklogic is able to hold an additional case insensitive index... maybe mongo does the same thing?
– RayLoveless
Dec 31 '12 at 17:05
Raymo, a case insensitive index feature does not exist today in Mongo, but it's being talked about. jira.mongodb.org/browse/SERVER-90
– Dan
Feb 19 '13 at 3:18
22
FYI, my answer is now obsolete. Mongodb now supports advanced full text searching, with many features. See docs.mongodb.org/manual/core/index-text
– Dan
Aug 11 '14 at 20:02
1
@Dan, just for the info, in latest MongoDB, "If an index exists for the field, then MongoDB matches the regular expression against the values in the index, which can be faster than a collection scan." -- docs.mongodb.org/manual/reference/operator/query/regex/…
– Sergiy Sokolenko
Nov 16 '15 at 13:28
@Dan, it should be noted, that the fancy new full text index has it's problems - "For the Latin alphabet, text indexes are case insensitive for non-diacritics; i.e. case insensitive for [A-z]. For all other characters, text indexes treat them as distinct."; So, for non Latin alphabet, it might be reasonable to use regex search which should also take an advantage of existing index (see my comment above).
– Sergiy Sokolenko
Nov 16 '15 at 13:32
|
show 4 more comments
Any documentation available that show how the indexes work? I'm asking because, if I recall, marklogic is able to hold an additional case insensitive index... maybe mongo does the same thing?
– RayLoveless
Dec 31 '12 at 17:05
Raymo, a case insensitive index feature does not exist today in Mongo, but it's being talked about. jira.mongodb.org/browse/SERVER-90
– Dan
Feb 19 '13 at 3:18
22
FYI, my answer is now obsolete. Mongodb now supports advanced full text searching, with many features. See docs.mongodb.org/manual/core/index-text
– Dan
Aug 11 '14 at 20:02
1
@Dan, just for the info, in latest MongoDB, "If an index exists for the field, then MongoDB matches the regular expression against the values in the index, which can be faster than a collection scan." -- docs.mongodb.org/manual/reference/operator/query/regex/…
– Sergiy Sokolenko
Nov 16 '15 at 13:28
@Dan, it should be noted, that the fancy new full text index has it's problems - "For the Latin alphabet, text indexes are case insensitive for non-diacritics; i.e. case insensitive for [A-z]. For all other characters, text indexes treat them as distinct."; So, for non Latin alphabet, it might be reasonable to use regex search which should also take an advantage of existing index (see my comment above).
– Sergiy Sokolenko
Nov 16 '15 at 13:32
Any documentation available that show how the indexes work? I'm asking because, if I recall, marklogic is able to hold an additional case insensitive index... maybe mongo does the same thing?
– RayLoveless
Dec 31 '12 at 17:05
Any documentation available that show how the indexes work? I'm asking because, if I recall, marklogic is able to hold an additional case insensitive index... maybe mongo does the same thing?
– RayLoveless
Dec 31 '12 at 17:05
Raymo, a case insensitive index feature does not exist today in Mongo, but it's being talked about. jira.mongodb.org/browse/SERVER-90
– Dan
Feb 19 '13 at 3:18
Raymo, a case insensitive index feature does not exist today in Mongo, but it's being talked about. jira.mongodb.org/browse/SERVER-90
– Dan
Feb 19 '13 at 3:18
22
22
FYI, my answer is now obsolete. Mongodb now supports advanced full text searching, with many features. See docs.mongodb.org/manual/core/index-text
– Dan
Aug 11 '14 at 20:02
FYI, my answer is now obsolete. Mongodb now supports advanced full text searching, with many features. See docs.mongodb.org/manual/core/index-text
– Dan
Aug 11 '14 at 20:02
1
1
@Dan, just for the info, in latest MongoDB, "If an index exists for the field, then MongoDB matches the regular expression against the values in the index, which can be faster than a collection scan." -- docs.mongodb.org/manual/reference/operator/query/regex/…
– Sergiy Sokolenko
Nov 16 '15 at 13:28
@Dan, just for the info, in latest MongoDB, "If an index exists for the field, then MongoDB matches the regular expression against the values in the index, which can be faster than a collection scan." -- docs.mongodb.org/manual/reference/operator/query/regex/…
– Sergiy Sokolenko
Nov 16 '15 at 13:28
@Dan, it should be noted, that the fancy new full text index has it's problems - "For the Latin alphabet, text indexes are case insensitive for non-diacritics; i.e. case insensitive for [A-z]. For all other characters, text indexes treat them as distinct."; So, for non Latin alphabet, it might be reasonable to use regex search which should also take an advantage of existing index (see my comment above).
– Sergiy Sokolenko
Nov 16 '15 at 13:32
@Dan, it should be noted, that the fancy new full text index has it's problems - "For the Latin alphabet, text indexes are case insensitive for non-diacritics; i.e. case insensitive for [A-z]. For all other characters, text indexes treat them as distinct."; So, for non Latin alphabet, it might be reasonable to use regex search which should also take an advantage of existing index (see my comment above).
– Sergiy Sokolenko
Nov 16 '15 at 13:32
|
show 4 more comments
up vote
58
down vote
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
add a comment |
up vote
58
down vote
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
add a comment |
up vote
58
down vote
up vote
58
down vote
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
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
answered Jun 3 '11 at 8:59
jflaflamme
1,573119
1,573119
add a comment |
add a comment |
up vote
54
down vote
If you need to create the regexp from a variable, this is a much better way to do it: https://stackoverflow.com/a/10728069/309514
You can then do something like:
var string = "SomeStringToFind";
var regex = new RegExp(["^", string, "$"].join(""), "i");
// Creates a regex of: /^SomeStringToFind$/i
db.stuff.find( { foo: regex } );
This has the benefit be being more programmatic or you can get a performance boost by compiling it ahead of time if you're reusing it a lot.
new RegExp("^" + req.params.term.toLowerCase(), "i")
also works fine
– Tahir Yasin
Mar 29 '17 at 15:43
you should consider escaping the string for increasing security if the variable comes from a request: stackoverflow.com/a/50633536/5195127
– davidivad
May 31 at 22:19
add a comment |
up vote
54
down vote
If you need to create the regexp from a variable, this is a much better way to do it: https://stackoverflow.com/a/10728069/309514
You can then do something like:
var string = "SomeStringToFind";
var regex = new RegExp(["^", string, "$"].join(""), "i");
// Creates a regex of: /^SomeStringToFind$/i
db.stuff.find( { foo: regex } );
This has the benefit be being more programmatic or you can get a performance boost by compiling it ahead of time if you're reusing it a lot.
new RegExp("^" + req.params.term.toLowerCase(), "i")
also works fine
– Tahir Yasin
Mar 29 '17 at 15:43
you should consider escaping the string for increasing security if the variable comes from a request: stackoverflow.com/a/50633536/5195127
– davidivad
May 31 at 22:19
add a comment |
up vote
54
down vote
up vote
54
down vote
If you need to create the regexp from a variable, this is a much better way to do it: https://stackoverflow.com/a/10728069/309514
You can then do something like:
var string = "SomeStringToFind";
var regex = new RegExp(["^", string, "$"].join(""), "i");
// Creates a regex of: /^SomeStringToFind$/i
db.stuff.find( { foo: regex } );
This has the benefit be being more programmatic or you can get a performance boost by compiling it ahead of time if you're reusing it a lot.
If you need to create the regexp from a variable, this is a much better way to do it: https://stackoverflow.com/a/10728069/309514
You can then do something like:
var string = "SomeStringToFind";
var regex = new RegExp(["^", string, "$"].join(""), "i");
// Creates a regex of: /^SomeStringToFind$/i
db.stuff.find( { foo: regex } );
This has the benefit be being more programmatic or you can get a performance boost by compiling it ahead of time if you're reusing it a lot.
edited May 23 '17 at 12:18
Community♦
11
11
answered Jul 12 '12 at 20:39
Fotios
2,6102329
2,6102329
new RegExp("^" + req.params.term.toLowerCase(), "i")
also works fine
– Tahir Yasin
Mar 29 '17 at 15:43
you should consider escaping the string for increasing security if the variable comes from a request: stackoverflow.com/a/50633536/5195127
– davidivad
May 31 at 22:19
add a comment |
new RegExp("^" + req.params.term.toLowerCase(), "i")
also works fine
– Tahir Yasin
Mar 29 '17 at 15:43
you should consider escaping the string for increasing security if the variable comes from a request: stackoverflow.com/a/50633536/5195127
– davidivad
May 31 at 22:19
new RegExp("^" + req.params.term.toLowerCase(), "i")
also works fine– Tahir Yasin
Mar 29 '17 at 15:43
new RegExp("^" + req.params.term.toLowerCase(), "i")
also works fine– Tahir Yasin
Mar 29 '17 at 15:43
you should consider escaping the string for increasing security if the variable comes from a request: stackoverflow.com/a/50633536/5195127
– davidivad
May 31 at 22:19
you should consider escaping the string for increasing security if the variable comes from a request: stackoverflow.com/a/50633536/5195127
– davidivad
May 31 at 22:19
add a comment |
up vote
23
down vote
As of Mongodb 3.4 you should use a case-insensitive collation index. This is the fastest way to do a case-insensitive search over datasets of increasingly large size. I personally emailed one of the founders to please get this working, and he made it happen! (It was an issue on JIRA for like 5 years, and many have requested the feature). Here's how it works:
A case-insensitive index is made by specifying a collation with a strength of either 1 or 2. You can create a case-insensitive index like this:
db.myCollection.createIndex({city: 1}, {collation: {locale: "en", strength: 2}});
Or you can do it for the whole collection by default when you create the database like so:
db.createCollection("Cities",{collation: {locale: "en",strength:2}});
And use it like this:
db.myCollection.find({city: "new york"}).collation({locale: "en", strength: 2});
This will return "New York", "new york", etc.
Alternatively, you can make all indexes use a collation by default when you make the collection like this:
db.createCollection("cities",{collation:{locale: "en", strength: 2}});
The benefit to this method is much improved efficiency and speed on larger datasets.
For more info: https://jira.mongodb.org/browse/SERVER-90 , https://docs.mongodb.com/manual/reference/collation/
An important point is that "To use the index, queries must specify the same collation", explicitly: docs.mongodb.com/manual/core/index-case-insensitive/…
– dskrvk
Sep 7 at 19:06
add a comment |
up vote
23
down vote
As of Mongodb 3.4 you should use a case-insensitive collation index. This is the fastest way to do a case-insensitive search over datasets of increasingly large size. I personally emailed one of the founders to please get this working, and he made it happen! (It was an issue on JIRA for like 5 years, and many have requested the feature). Here's how it works:
A case-insensitive index is made by specifying a collation with a strength of either 1 or 2. You can create a case-insensitive index like this:
db.myCollection.createIndex({city: 1}, {collation: {locale: "en", strength: 2}});
Or you can do it for the whole collection by default when you create the database like so:
db.createCollection("Cities",{collation: {locale: "en",strength:2}});
And use it like this:
db.myCollection.find({city: "new york"}).collation({locale: "en", strength: 2});
This will return "New York", "new york", etc.
Alternatively, you can make all indexes use a collation by default when you make the collection like this:
db.createCollection("cities",{collation:{locale: "en", strength: 2}});
The benefit to this method is much improved efficiency and speed on larger datasets.
For more info: https://jira.mongodb.org/browse/SERVER-90 , https://docs.mongodb.com/manual/reference/collation/
An important point is that "To use the index, queries must specify the same collation", explicitly: docs.mongodb.com/manual/core/index-case-insensitive/…
– dskrvk
Sep 7 at 19:06
add a comment |
up vote
23
down vote
up vote
23
down vote
As of Mongodb 3.4 you should use a case-insensitive collation index. This is the fastest way to do a case-insensitive search over datasets of increasingly large size. I personally emailed one of the founders to please get this working, and he made it happen! (It was an issue on JIRA for like 5 years, and many have requested the feature). Here's how it works:
A case-insensitive index is made by specifying a collation with a strength of either 1 or 2. You can create a case-insensitive index like this:
db.myCollection.createIndex({city: 1}, {collation: {locale: "en", strength: 2}});
Or you can do it for the whole collection by default when you create the database like so:
db.createCollection("Cities",{collation: {locale: "en",strength:2}});
And use it like this:
db.myCollection.find({city: "new york"}).collation({locale: "en", strength: 2});
This will return "New York", "new york", etc.
Alternatively, you can make all indexes use a collation by default when you make the collection like this:
db.createCollection("cities",{collation:{locale: "en", strength: 2}});
The benefit to this method is much improved efficiency and speed on larger datasets.
For more info: https://jira.mongodb.org/browse/SERVER-90 , https://docs.mongodb.com/manual/reference/collation/
As of Mongodb 3.4 you should use a case-insensitive collation index. This is the fastest way to do a case-insensitive search over datasets of increasingly large size. I personally emailed one of the founders to please get this working, and he made it happen! (It was an issue on JIRA for like 5 years, and many have requested the feature). Here's how it works:
A case-insensitive index is made by specifying a collation with a strength of either 1 or 2. You can create a case-insensitive index like this:
db.myCollection.createIndex({city: 1}, {collation: {locale: "en", strength: 2}});
Or you can do it for the whole collection by default when you create the database like so:
db.createCollection("Cities",{collation: {locale: "en",strength:2}});
And use it like this:
db.myCollection.find({city: "new york"}).collation({locale: "en", strength: 2});
This will return "New York", "new york", etc.
Alternatively, you can make all indexes use a collation by default when you make the collection like this:
db.createCollection("cities",{collation:{locale: "en", strength: 2}});
The benefit to this method is much improved efficiency and speed on larger datasets.
For more info: https://jira.mongodb.org/browse/SERVER-90 , https://docs.mongodb.com/manual/reference/collation/
edited Apr 5 '17 at 11:56
answered Dec 1 '16 at 15:47
user3413723
3,8343037
3,8343037
An important point is that "To use the index, queries must specify the same collation", explicitly: docs.mongodb.com/manual/core/index-case-insensitive/…
– dskrvk
Sep 7 at 19:06
add a comment |
An important point is that "To use the index, queries must specify the same collation", explicitly: docs.mongodb.com/manual/core/index-case-insensitive/…
– dskrvk
Sep 7 at 19:06
An important point is that "To use the index, queries must specify the same collation", explicitly: docs.mongodb.com/manual/core/index-case-insensitive/…
– dskrvk
Sep 7 at 19:06
An important point is that "To use the index, queries must specify the same collation", explicitly: docs.mongodb.com/manual/core/index-case-insensitive/…
– dskrvk
Sep 7 at 19:06
add a comment |
up vote
16
down vote
db.zipcodes.find({city : "NEW YORK"}); // Case-sensitive
db.zipcodes.find({city : /NEW york/i}); // Note the 'i' flag for case-insensitivity
1
Please add description to code.
– Parth Trivedi
Dec 17 '15 at 13:02
6
@ParthTrivedi, comments are about as long as the code itself. Do you want 3 pages essay or something?
– Oleg V. Volkov
Dec 17 '15 at 14:22
@OlegV.Volkov must have description about how your answer appropriate and what is wrong in questioner code.
– Parth Trivedi
Dec 18 '15 at 4:39
add a comment |
up vote
16
down vote
db.zipcodes.find({city : "NEW YORK"}); // Case-sensitive
db.zipcodes.find({city : /NEW york/i}); // Note the 'i' flag for case-insensitivity
1
Please add description to code.
– Parth Trivedi
Dec 17 '15 at 13:02
6
@ParthTrivedi, comments are about as long as the code itself. Do you want 3 pages essay or something?
– Oleg V. Volkov
Dec 17 '15 at 14:22
@OlegV.Volkov must have description about how your answer appropriate and what is wrong in questioner code.
– Parth Trivedi
Dec 18 '15 at 4:39
add a comment |
up vote
16
down vote
up vote
16
down vote
db.zipcodes.find({city : "NEW YORK"}); // Case-sensitive
db.zipcodes.find({city : /NEW york/i}); // Note the 'i' flag for case-insensitivity
db.zipcodes.find({city : "NEW YORK"}); // Case-sensitive
db.zipcodes.find({city : /NEW york/i}); // Note the 'i' flag for case-insensitivity
edited Dec 17 '15 at 13:56
Maneating Koala
1,96032139
1,96032139
answered Dec 17 '15 at 12:57
rshivamca
17112
17112
1
Please add description to code.
– Parth Trivedi
Dec 17 '15 at 13:02
6
@ParthTrivedi, comments are about as long as the code itself. Do you want 3 pages essay or something?
– Oleg V. Volkov
Dec 17 '15 at 14:22
@OlegV.Volkov must have description about how your answer appropriate and what is wrong in questioner code.
– Parth Trivedi
Dec 18 '15 at 4:39
add a comment |
1
Please add description to code.
– Parth Trivedi
Dec 17 '15 at 13:02
6
@ParthTrivedi, comments are about as long as the code itself. Do you want 3 pages essay or something?
– Oleg V. Volkov
Dec 17 '15 at 14:22
@OlegV.Volkov must have description about how your answer appropriate and what is wrong in questioner code.
– Parth Trivedi
Dec 18 '15 at 4:39
1
1
Please add description to code.
– Parth Trivedi
Dec 17 '15 at 13:02
Please add description to code.
– Parth Trivedi
Dec 17 '15 at 13:02
6
6
@ParthTrivedi, comments are about as long as the code itself. Do you want 3 pages essay or something?
– Oleg V. Volkov
Dec 17 '15 at 14:22
@ParthTrivedi, comments are about as long as the code itself. Do you want 3 pages essay or something?
– Oleg V. Volkov
Dec 17 '15 at 14:22
@OlegV.Volkov must have description about how your answer appropriate and what is wrong in questioner code.
– Parth Trivedi
Dec 18 '15 at 4:39
@OlegV.Volkov must have description about how your answer appropriate and what is wrong in questioner code.
– Parth Trivedi
Dec 18 '15 at 4:39
add a comment |
up vote
12
down vote
TL;DR
Correct way to do this in mongo
Do not Use RegExp
Go natural And use mongodb's inbuilt indexing , search
Step 1 :
db.articles.insert(
[
{ _id: 1, subject: "coffee", author: "xyz", views: 50 },
{ _id: 2, subject: "Coffee Shopping", author: "efg", views: 5 },
{ _id: 3, subject: "Baking a cake", author: "abc", views: 90 },
{ _id: 4, subject: "baking", author: "xyz", views: 100 },
{ _id: 5, subject: "Café Con Leche", author: "abc", views: 200 },
{ _id: 6, subject: "Сырники", author: "jkl", views: 80 },
{ _id: 7, subject: "coffee and cream", author: "efg", views: 10 },
{ _id: 8, subject: "Cafe con Leche", author: "xyz", views: 10 }
]
)
Step 2 :
Need to create index on whichever TEXT field you want to search , without indexing query will be extremely slow
db.articles.createIndex( { subject: "text" } )
step 3 :
db.articles.find( { $text: { $search: "coffee",$caseSensitive :true } } ) //FOR SENSITIVITY
db.articles.find( { $text: { $search: "coffee",$caseSensitive :false } } ) //FOR INSENSITIVITY
1
Good option, but there's nothing more "correct" about using a text index versus a regex, it's just another option. It's overkill for the OP's case.
– JohnnyHK
Aug 28 '16 at 2:16
1
Except regex is significantly slower. Fulltext searching is also slow, but not as slow. The fastest (but more bloated) way would be a separate field which is always set to lowercase.
– Tom Mettam
Jan 17 at 0:37
add a comment |
up vote
12
down vote
TL;DR
Correct way to do this in mongo
Do not Use RegExp
Go natural And use mongodb's inbuilt indexing , search
Step 1 :
db.articles.insert(
[
{ _id: 1, subject: "coffee", author: "xyz", views: 50 },
{ _id: 2, subject: "Coffee Shopping", author: "efg", views: 5 },
{ _id: 3, subject: "Baking a cake", author: "abc", views: 90 },
{ _id: 4, subject: "baking", author: "xyz", views: 100 },
{ _id: 5, subject: "Café Con Leche", author: "abc", views: 200 },
{ _id: 6, subject: "Сырники", author: "jkl", views: 80 },
{ _id: 7, subject: "coffee and cream", author: "efg", views: 10 },
{ _id: 8, subject: "Cafe con Leche", author: "xyz", views: 10 }
]
)
Step 2 :
Need to create index on whichever TEXT field you want to search , without indexing query will be extremely slow
db.articles.createIndex( { subject: "text" } )
step 3 :
db.articles.find( { $text: { $search: "coffee",$caseSensitive :true } } ) //FOR SENSITIVITY
db.articles.find( { $text: { $search: "coffee",$caseSensitive :false } } ) //FOR INSENSITIVITY
1
Good option, but there's nothing more "correct" about using a text index versus a regex, it's just another option. It's overkill for the OP's case.
– JohnnyHK
Aug 28 '16 at 2:16
1
Except regex is significantly slower. Fulltext searching is also slow, but not as slow. The fastest (but more bloated) way would be a separate field which is always set to lowercase.
– Tom Mettam
Jan 17 at 0:37
add a comment |
up vote
12
down vote
up vote
12
down vote
TL;DR
Correct way to do this in mongo
Do not Use RegExp
Go natural And use mongodb's inbuilt indexing , search
Step 1 :
db.articles.insert(
[
{ _id: 1, subject: "coffee", author: "xyz", views: 50 },
{ _id: 2, subject: "Coffee Shopping", author: "efg", views: 5 },
{ _id: 3, subject: "Baking a cake", author: "abc", views: 90 },
{ _id: 4, subject: "baking", author: "xyz", views: 100 },
{ _id: 5, subject: "Café Con Leche", author: "abc", views: 200 },
{ _id: 6, subject: "Сырники", author: "jkl", views: 80 },
{ _id: 7, subject: "coffee and cream", author: "efg", views: 10 },
{ _id: 8, subject: "Cafe con Leche", author: "xyz", views: 10 }
]
)
Step 2 :
Need to create index on whichever TEXT field you want to search , without indexing query will be extremely slow
db.articles.createIndex( { subject: "text" } )
step 3 :
db.articles.find( { $text: { $search: "coffee",$caseSensitive :true } } ) //FOR SENSITIVITY
db.articles.find( { $text: { $search: "coffee",$caseSensitive :false } } ) //FOR INSENSITIVITY
TL;DR
Correct way to do this in mongo
Do not Use RegExp
Go natural And use mongodb's inbuilt indexing , search
Step 1 :
db.articles.insert(
[
{ _id: 1, subject: "coffee", author: "xyz", views: 50 },
{ _id: 2, subject: "Coffee Shopping", author: "efg", views: 5 },
{ _id: 3, subject: "Baking a cake", author: "abc", views: 90 },
{ _id: 4, subject: "baking", author: "xyz", views: 100 },
{ _id: 5, subject: "Café Con Leche", author: "abc", views: 200 },
{ _id: 6, subject: "Сырники", author: "jkl", views: 80 },
{ _id: 7, subject: "coffee and cream", author: "efg", views: 10 },
{ _id: 8, subject: "Cafe con Leche", author: "xyz", views: 10 }
]
)
Step 2 :
Need to create index on whichever TEXT field you want to search , without indexing query will be extremely slow
db.articles.createIndex( { subject: "text" } )
step 3 :
db.articles.find( { $text: { $search: "coffee",$caseSensitive :true } } ) //FOR SENSITIVITY
db.articles.find( { $text: { $search: "coffee",$caseSensitive :false } } ) //FOR INSENSITIVITY
answered Aug 27 '16 at 19:29
vijay
3,67913048
3,67913048
1
Good option, but there's nothing more "correct" about using a text index versus a regex, it's just another option. It's overkill for the OP's case.
– JohnnyHK
Aug 28 '16 at 2:16
1
Except regex is significantly slower. Fulltext searching is also slow, but not as slow. The fastest (but more bloated) way would be a separate field which is always set to lowercase.
– Tom Mettam
Jan 17 at 0:37
add a comment |
1
Good option, but there's nothing more "correct" about using a text index versus a regex, it's just another option. It's overkill for the OP's case.
– JohnnyHK
Aug 28 '16 at 2:16
1
Except regex is significantly slower. Fulltext searching is also slow, but not as slow. The fastest (but more bloated) way would be a separate field which is always set to lowercase.
– Tom Mettam
Jan 17 at 0:37
1
1
Good option, but there's nothing more "correct" about using a text index versus a regex, it's just another option. It's overkill for the OP's case.
– JohnnyHK
Aug 28 '16 at 2:16
Good option, but there's nothing more "correct" about using a text index versus a regex, it's just another option. It's overkill for the OP's case.
– JohnnyHK
Aug 28 '16 at 2:16
1
1
Except regex is significantly slower. Fulltext searching is also slow, but not as slow. The fastest (but more bloated) way would be a separate field which is always set to lowercase.
– Tom Mettam
Jan 17 at 0:37
Except regex is significantly slower. Fulltext searching is also slow, but not as slow. The fastest (but more bloated) way would be a separate field which is always set to lowercase.
– Tom Mettam
Jan 17 at 0:37
add a comment |
up vote
9
down vote
Mongo (current version 2.0.0) doesn't allow case-insensitive searches against indexed fields - see their documentation. For non-indexed fields, the regexes listed in the other answers should be fine.
18
Just to clarify this: case-insensitive searches are allowed on indexed fields, they just won't use the index and will be as slow as if the field wasn't indexed.
– heavi5ide
Dec 19 '11 at 17:22
@heavi5ide since this question is being used to mark duplicates I thought I would clarify that regexes (needed for case insensitive searches) do use the index, however, they must do a full index scan. In other words they cannot efficiently use the index. Fortunately the documentation has since been updated from 2011 but still good to note here too.
– Sammaye
Aug 13 '14 at 17:37
add a comment |
up vote
9
down vote
Mongo (current version 2.0.0) doesn't allow case-insensitive searches against indexed fields - see their documentation. For non-indexed fields, the regexes listed in the other answers should be fine.
18
Just to clarify this: case-insensitive searches are allowed on indexed fields, they just won't use the index and will be as slow as if the field wasn't indexed.
– heavi5ide
Dec 19 '11 at 17:22
@heavi5ide since this question is being used to mark duplicates I thought I would clarify that regexes (needed for case insensitive searches) do use the index, however, they must do a full index scan. In other words they cannot efficiently use the index. Fortunately the documentation has since been updated from 2011 but still good to note here too.
– Sammaye
Aug 13 '14 at 17:37
add a comment |
up vote
9
down vote
up vote
9
down vote
Mongo (current version 2.0.0) doesn't allow case-insensitive searches against indexed fields - see their documentation. For non-indexed fields, the regexes listed in the other answers should be fine.
Mongo (current version 2.0.0) doesn't allow case-insensitive searches against indexed fields - see their documentation. For non-indexed fields, the regexes listed in the other answers should be fine.
answered Oct 24 '11 at 19:31
Aidan Feldman
2,5802434
2,5802434
18
Just to clarify this: case-insensitive searches are allowed on indexed fields, they just won't use the index and will be as slow as if the field wasn't indexed.
– heavi5ide
Dec 19 '11 at 17:22
@heavi5ide since this question is being used to mark duplicates I thought I would clarify that regexes (needed for case insensitive searches) do use the index, however, they must do a full index scan. In other words they cannot efficiently use the index. Fortunately the documentation has since been updated from 2011 but still good to note here too.
– Sammaye
Aug 13 '14 at 17:37
add a comment |
18
Just to clarify this: case-insensitive searches are allowed on indexed fields, they just won't use the index and will be as slow as if the field wasn't indexed.
– heavi5ide
Dec 19 '11 at 17:22
@heavi5ide since this question is being used to mark duplicates I thought I would clarify that regexes (needed for case insensitive searches) do use the index, however, they must do a full index scan. In other words they cannot efficiently use the index. Fortunately the documentation has since been updated from 2011 but still good to note here too.
– Sammaye
Aug 13 '14 at 17:37
18
18
Just to clarify this: case-insensitive searches are allowed on indexed fields, they just won't use the index and will be as slow as if the field wasn't indexed.
– heavi5ide
Dec 19 '11 at 17:22
Just to clarify this: case-insensitive searches are allowed on indexed fields, they just won't use the index and will be as slow as if the field wasn't indexed.
– heavi5ide
Dec 19 '11 at 17:22
@heavi5ide since this question is being used to mark duplicates I thought I would clarify that regexes (needed for case insensitive searches) do use the index, however, they must do a full index scan. In other words they cannot efficiently use the index. Fortunately the documentation has since been updated from 2011 but still good to note here too.
– Sammaye
Aug 13 '14 at 17:37
@heavi5ide since this question is being used to mark duplicates I thought I would clarify that regexes (needed for case insensitive searches) do use the index, however, they must do a full index scan. In other words they cannot efficiently use the index. Fortunately the documentation has since been updated from 2011 but still good to note here too.
– Sammaye
Aug 13 '14 at 17:37
add a comment |
up vote
7
down vote
Using Mongoose this worked for me:
var find = function(username, next){
User.find({'username': {$regex: new RegExp('^' + username, 'i')}}, function(err, res){
if(err) throw err;
next(null, res);
});
}
8
Isn't the.toLowerCase()
redundant if you're specifying the case-insensitive flag ofi
?
– k00k
Jul 1 '15 at 15:01
Yes it is. You don't need .toLowerCase(). I have removed it from the answer.
– ChrisRich
May 18 '17 at 1:00
hmm should this work like that? When I search for "mark" it also gets every record with "marko" - is there a way only ignore case-sensitivity?
– Suisse
Jun 19 '17 at 0:54
Ok found it, the correct regex would be: '^' + serach_name + '$', "i"
– Suisse
Jun 19 '17 at 1:02
1
This is DANGEROUS. You are not escaping username, so any arbitrary regex can be injected.
– Tom Mettam
Jan 17 at 0:37
add a comment |
up vote
7
down vote
Using Mongoose this worked for me:
var find = function(username, next){
User.find({'username': {$regex: new RegExp('^' + username, 'i')}}, function(err, res){
if(err) throw err;
next(null, res);
});
}
8
Isn't the.toLowerCase()
redundant if you're specifying the case-insensitive flag ofi
?
– k00k
Jul 1 '15 at 15:01
Yes it is. You don't need .toLowerCase(). I have removed it from the answer.
– ChrisRich
May 18 '17 at 1:00
hmm should this work like that? When I search for "mark" it also gets every record with "marko" - is there a way only ignore case-sensitivity?
– Suisse
Jun 19 '17 at 0:54
Ok found it, the correct regex would be: '^' + serach_name + '$', "i"
– Suisse
Jun 19 '17 at 1:02
1
This is DANGEROUS. You are not escaping username, so any arbitrary regex can be injected.
– Tom Mettam
Jan 17 at 0:37
add a comment |
up vote
7
down vote
up vote
7
down vote
Using Mongoose this worked for me:
var find = function(username, next){
User.find({'username': {$regex: new RegExp('^' + username, 'i')}}, function(err, res){
if(err) throw err;
next(null, res);
});
}
Using Mongoose this worked for me:
var find = function(username, next){
User.find({'username': {$regex: new RegExp('^' + username, 'i')}}, function(err, res){
if(err) throw err;
next(null, res);
});
}
edited May 18 '17 at 1:01
answered Oct 16 '14 at 4:54
ChrisRich
2,37662435
2,37662435
8
Isn't the.toLowerCase()
redundant if you're specifying the case-insensitive flag ofi
?
– k00k
Jul 1 '15 at 15:01
Yes it is. You don't need .toLowerCase(). I have removed it from the answer.
– ChrisRich
May 18 '17 at 1:00
hmm should this work like that? When I search for "mark" it also gets every record with "marko" - is there a way only ignore case-sensitivity?
– Suisse
Jun 19 '17 at 0:54
Ok found it, the correct regex would be: '^' + serach_name + '$', "i"
– Suisse
Jun 19 '17 at 1:02
1
This is DANGEROUS. You are not escaping username, so any arbitrary regex can be injected.
– Tom Mettam
Jan 17 at 0:37
add a comment |
8
Isn't the.toLowerCase()
redundant if you're specifying the case-insensitive flag ofi
?
– k00k
Jul 1 '15 at 15:01
Yes it is. You don't need .toLowerCase(). I have removed it from the answer.
– ChrisRich
May 18 '17 at 1:00
hmm should this work like that? When I search for "mark" it also gets every record with "marko" - is there a way only ignore case-sensitivity?
– Suisse
Jun 19 '17 at 0:54
Ok found it, the correct regex would be: '^' + serach_name + '$', "i"
– Suisse
Jun 19 '17 at 1:02
1
This is DANGEROUS. You are not escaping username, so any arbitrary regex can be injected.
– Tom Mettam
Jan 17 at 0:37
8
8
Isn't the
.toLowerCase()
redundant if you're specifying the case-insensitive flag of i
?– k00k
Jul 1 '15 at 15:01
Isn't the
.toLowerCase()
redundant if you're specifying the case-insensitive flag of i
?– k00k
Jul 1 '15 at 15:01
Yes it is. You don't need .toLowerCase(). I have removed it from the answer.
– ChrisRich
May 18 '17 at 1:00
Yes it is. You don't need .toLowerCase(). I have removed it from the answer.
– ChrisRich
May 18 '17 at 1:00
hmm should this work like that? When I search for "mark" it also gets every record with "marko" - is there a way only ignore case-sensitivity?
– Suisse
Jun 19 '17 at 0:54
hmm should this work like that? When I search for "mark" it also gets every record with "marko" - is there a way only ignore case-sensitivity?
– Suisse
Jun 19 '17 at 0:54
Ok found it, the correct regex would be: '^' + serach_name + '$', "i"
– Suisse
Jun 19 '17 at 1:02
Ok found it, the correct regex would be: '^' + serach_name + '$', "i"
– Suisse
Jun 19 '17 at 1:02
1
1
This is DANGEROUS. You are not escaping username, so any arbitrary regex can be injected.
– Tom Mettam
Jan 17 at 0:37
This is DANGEROUS. You are not escaping username, so any arbitrary regex can be injected.
– Tom Mettam
Jan 17 at 0:37
add a comment |
up vote
5
down vote
The best method is in your language of choice, when creating a model wrapper for your objects, have your save() method iterate through a set of fields that you will be searching on that are also indexed; those set of fields should have lowercase counterparts that are then used for searching.
Every time the object is saved again, the lowercase properties are then checked and updated with any changes to the main properties. This will make it so you can search efficiently, but hide the extra work needed to update the lc fields each time.
The lower case fields could be a key:value object store or just the field name with a prefixed lc_. I use the second one to simplify querying (deep object querying can be confusing at times).
Note: you want to index the lc_ fields, not the main fields they are based off of.
add a comment |
up vote
5
down vote
The best method is in your language of choice, when creating a model wrapper for your objects, have your save() method iterate through a set of fields that you will be searching on that are also indexed; those set of fields should have lowercase counterparts that are then used for searching.
Every time the object is saved again, the lowercase properties are then checked and updated with any changes to the main properties. This will make it so you can search efficiently, but hide the extra work needed to update the lc fields each time.
The lower case fields could be a key:value object store or just the field name with a prefixed lc_. I use the second one to simplify querying (deep object querying can be confusing at times).
Note: you want to index the lc_ fields, not the main fields they are based off of.
add a comment |
up vote
5
down vote
up vote
5
down vote
The best method is in your language of choice, when creating a model wrapper for your objects, have your save() method iterate through a set of fields that you will be searching on that are also indexed; those set of fields should have lowercase counterparts that are then used for searching.
Every time the object is saved again, the lowercase properties are then checked and updated with any changes to the main properties. This will make it so you can search efficiently, but hide the extra work needed to update the lc fields each time.
The lower case fields could be a key:value object store or just the field name with a prefixed lc_. I use the second one to simplify querying (deep object querying can be confusing at times).
Note: you want to index the lc_ fields, not the main fields they are based off of.
The best method is in your language of choice, when creating a model wrapper for your objects, have your save() method iterate through a set of fields that you will be searching on that are also indexed; those set of fields should have lowercase counterparts that are then used for searching.
Every time the object is saved again, the lowercase properties are then checked and updated with any changes to the main properties. This will make it so you can search efficiently, but hide the extra work needed to update the lc fields each time.
The lower case fields could be a key:value object store or just the field name with a prefixed lc_. I use the second one to simplify querying (deep object querying can be confusing at times).
Note: you want to index the lc_ fields, not the main fields they are based off of.
answered Apr 20 '11 at 17:30
RobKohr
3,34653650
3,34653650
add a comment |
add a comment |
up vote
5
down vote
Suppose you want to search "column" in "Table" and you want case insenstive search. The best and efficient way is as below;
//create empty JSON Object
mycolumn = {};
//check if column has valid value
if(column) {
mycolumn.column = {$regex: new RegExp(column), $options: "i"};
}
Table.find(mycolumn);
Above code just adds your search value as RegEx and searches in with insensitve criteria set with "i" as option.
All the best.
add a comment |
up vote
5
down vote
Suppose you want to search "column" in "Table" and you want case insenstive search. The best and efficient way is as below;
//create empty JSON Object
mycolumn = {};
//check if column has valid value
if(column) {
mycolumn.column = {$regex: new RegExp(column), $options: "i"};
}
Table.find(mycolumn);
Above code just adds your search value as RegEx and searches in with insensitve criteria set with "i" as option.
All the best.
add a comment |
up vote
5
down vote
up vote
5
down vote
Suppose you want to search "column" in "Table" and you want case insenstive search. The best and efficient way is as below;
//create empty JSON Object
mycolumn = {};
//check if column has valid value
if(column) {
mycolumn.column = {$regex: new RegExp(column), $options: "i"};
}
Table.find(mycolumn);
Above code just adds your search value as RegEx and searches in with insensitve criteria set with "i" as option.
All the best.
Suppose you want to search "column" in "Table" and you want case insenstive search. The best and efficient way is as below;
//create empty JSON Object
mycolumn = {};
//check if column has valid value
if(column) {
mycolumn.column = {$regex: new RegExp(column), $options: "i"};
}
Table.find(mycolumn);
Above code just adds your search value as RegEx and searches in with insensitve criteria set with "i" as option.
All the best.
answered Apr 30 '16 at 14:44
Ankur Soni
2,56411633
2,56411633
add a comment |
add a comment |
up vote
5
down vote
One very important thing to keep in mind when using a Regex based query - When you are doing this for a login system, escape every single character you are searching for, and don't forget the ^ and $ operators. Lodash has a nice function for this, should you be using it already:
db.stuff.find({$regex: new RegExp(_.escapeRegExp(bar), $options: 'i'})
Why? Imagine a user entering .*
as his username. That would match all usernames, enabling a login by just guessing any user's password.
add a comment |
up vote
5
down vote
One very important thing to keep in mind when using a Regex based query - When you are doing this for a login system, escape every single character you are searching for, and don't forget the ^ and $ operators. Lodash has a nice function for this, should you be using it already:
db.stuff.find({$regex: new RegExp(_.escapeRegExp(bar), $options: 'i'})
Why? Imagine a user entering .*
as his username. That would match all usernames, enabling a login by just guessing any user's password.
add a comment |
up vote
5
down vote
up vote
5
down vote
One very important thing to keep in mind when using a Regex based query - When you are doing this for a login system, escape every single character you are searching for, and don't forget the ^ and $ operators. Lodash has a nice function for this, should you be using it already:
db.stuff.find({$regex: new RegExp(_.escapeRegExp(bar), $options: 'i'})
Why? Imagine a user entering .*
as his username. That would match all usernames, enabling a login by just guessing any user's password.
One very important thing to keep in mind when using a Regex based query - When you are doing this for a login system, escape every single character you are searching for, and don't forget the ^ and $ operators. Lodash has a nice function for this, should you be using it already:
db.stuff.find({$regex: new RegExp(_.escapeRegExp(bar), $options: 'i'})
Why? Imagine a user entering .*
as his username. That would match all usernames, enabling a login by just guessing any user's password.
edited May 23 '17 at 12:18
Community♦
11
11
answered May 6 '16 at 9:01
Ziao
5112
5112
add a comment |
add a comment |
up vote
4
down vote
db.company_profile.find({ "companyName" : { "$regex" : "Nilesh" , "$options" : "i"}});
add a comment |
up vote
4
down vote
db.company_profile.find({ "companyName" : { "$regex" : "Nilesh" , "$options" : "i"}});
add a comment |
up vote
4
down vote
up vote
4
down vote
db.company_profile.find({ "companyName" : { "$regex" : "Nilesh" , "$options" : "i"}});
db.company_profile.find({ "companyName" : { "$regex" : "Nilesh" , "$options" : "i"}});
answered Sep 4 at 14:37
Nilesh
84311128
84311128
add a comment |
add a comment |
up vote
3
down vote
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.
Here's the official document on the aggregation command operator: https://docs.mongodb.com/manual/reference/operator/aggregation/strcasecmp/#exp._S_strcasecmp .
2
how to use this in a find() query? db.stuff.find({ name: $strcasecmp(name)}) ?
– Suisse
Jun 19 '17 at 0:51
add a comment |
up vote
3
down vote
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.
Here's the official document on the aggregation command operator: https://docs.mongodb.com/manual/reference/operator/aggregation/strcasecmp/#exp._S_strcasecmp .
2
how to use this in a find() query? db.stuff.find({ name: $strcasecmp(name)}) ?
– Suisse
Jun 19 '17 at 0:51
add a comment |
up vote
3
down vote
up vote
3
down vote
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.
Here's the official document on the aggregation command operator: https://docs.mongodb.com/manual/reference/operator/aggregation/strcasecmp/#exp._S_strcasecmp .
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.
Here's the official document on the aggregation command operator: https://docs.mongodb.com/manual/reference/operator/aggregation/strcasecmp/#exp._S_strcasecmp .
edited May 20 '17 at 12:44
answered May 20 '17 at 4:20
Jogue Wasin
364
364
2
how to use this in a find() query? db.stuff.find({ name: $strcasecmp(name)}) ?
– Suisse
Jun 19 '17 at 0:51
add a comment |
2
how to use this in a find() query? db.stuff.find({ name: $strcasecmp(name)}) ?
– Suisse
Jun 19 '17 at 0:51
2
2
how to use this in a find() query? db.stuff.find({ name: $strcasecmp(name)}) ?
– Suisse
Jun 19 '17 at 0:51
how to use this in a find() query? db.stuff.find({ name: $strcasecmp(name)}) ?
– Suisse
Jun 19 '17 at 0:51
add a comment |
up vote
1
down vote
Using a filter works for me in C#.
string s = "searchTerm";
var filter = Builders<Model>.Filter.Where(p => p.Title.ToLower().Contains(s.ToLower()));
var listSorted = collection.Find(filter).ToList();
var list = collection.Find(filter).ToList();
It may even use the index because I believe the methods are called after the return happens but I haven't tested this out yet.
This also avoids a problem of
var filter = Builders<Model>.Filter.Eq(p => p.Title.ToLower(), s.ToLower());
that mongodb will think p.Title.ToLower() is a property and won't map properly.
Thanks, It works for Me. Here we need to obtain filter in variable then pass in Find() method.
– Nilay
Aug 25 at 11:33
add a comment |
up vote
1
down vote
Using a filter works for me in C#.
string s = "searchTerm";
var filter = Builders<Model>.Filter.Where(p => p.Title.ToLower().Contains(s.ToLower()));
var listSorted = collection.Find(filter).ToList();
var list = collection.Find(filter).ToList();
It may even use the index because I believe the methods are called after the return happens but I haven't tested this out yet.
This also avoids a problem of
var filter = Builders<Model>.Filter.Eq(p => p.Title.ToLower(), s.ToLower());
that mongodb will think p.Title.ToLower() is a property and won't map properly.
Thanks, It works for Me. Here we need to obtain filter in variable then pass in Find() method.
– Nilay
Aug 25 at 11:33
add a comment |
up vote
1
down vote
up vote
1
down vote
Using a filter works for me in C#.
string s = "searchTerm";
var filter = Builders<Model>.Filter.Where(p => p.Title.ToLower().Contains(s.ToLower()));
var listSorted = collection.Find(filter).ToList();
var list = collection.Find(filter).ToList();
It may even use the index because I believe the methods are called after the return happens but I haven't tested this out yet.
This also avoids a problem of
var filter = Builders<Model>.Filter.Eq(p => p.Title.ToLower(), s.ToLower());
that mongodb will think p.Title.ToLower() is a property and won't map properly.
Using a filter works for me in C#.
string s = "searchTerm";
var filter = Builders<Model>.Filter.Where(p => p.Title.ToLower().Contains(s.ToLower()));
var listSorted = collection.Find(filter).ToList();
var list = collection.Find(filter).ToList();
It may even use the index because I believe the methods are called after the return happens but I haven't tested this out yet.
This also avoids a problem of
var filter = Builders<Model>.Filter.Eq(p => p.Title.ToLower(), s.ToLower());
that mongodb will think p.Title.ToLower() is a property and won't map properly.
answered Aug 7 '17 at 17:09
A_Arnold
1,2601031
1,2601031
Thanks, It works for Me. Here we need to obtain filter in variable then pass in Find() method.
– Nilay
Aug 25 at 11:33
add a comment |
Thanks, It works for Me. Here we need to obtain filter in variable then pass in Find() method.
– Nilay
Aug 25 at 11:33
Thanks, It works for Me. Here we need to obtain filter in variable then pass in Find() method.
– Nilay
Aug 25 at 11:33
Thanks, It works for Me. Here we need to obtain filter in variable then pass in Find() method.
– Nilay
Aug 25 at 11:33
add a comment |
up vote
1
down vote
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
There seems minor syntax issue(missing Braces).Please update the query:db.users.createIndex( { name: 1 }, {collation: { locale: 'tr', strength: 2 } } )
– Mohd Belal
Apr 12 at 11:56
add a comment |
up vote
1
down vote
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
There seems minor syntax issue(missing Braces).Please update the query:db.users.createIndex( { name: 1 }, {collation: { locale: 'tr', strength: 2 } } )
– Mohd Belal
Apr 12 at 11:56
add a comment |
up vote
1
down vote
up vote
1
down vote
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
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
answered Nov 22 '17 at 16:19
Gencebay D.
3082410
3082410
There seems minor syntax issue(missing Braces).Please update the query:db.users.createIndex( { name: 1 }, {collation: { locale: 'tr', strength: 2 } } )
– Mohd Belal
Apr 12 at 11:56
add a comment |
There seems minor syntax issue(missing Braces).Please update the query:db.users.createIndex( { name: 1 }, {collation: { locale: 'tr', strength: 2 } } )
– Mohd Belal
Apr 12 at 11:56
There seems minor syntax issue(missing Braces).Please update the query:
db.users.createIndex( { name: 1 }, {collation: { locale: 'tr', strength: 2 } } )
– Mohd Belal
Apr 12 at 11:56
There seems minor syntax issue(missing Braces).Please update the query:
db.users.createIndex( { name: 1 }, {collation: { locale: 'tr', strength: 2 } } )
– Mohd Belal
Apr 12 at 11:56
add a comment |
up vote
1
down vote
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
add a comment |
up vote
1
down vote
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
add a comment |
up vote
1
down vote
up vote
1
down vote
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
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
answered May 31 at 22:14
davidivad
4671616
4671616
add a comment |
add a comment |
up vote
0
down vote
I've created a simple Func for the case insensitive regex, which I use in my filter.
private Func<string, BsonRegularExpression> CaseInsensitiveCompare = (field) =>
BsonRegularExpression.Create(new Regex(field, RegexOptions.IgnoreCase));
Then you simply filter on a field as follows.
db.stuff.find({"foo": CaseInsensitiveCompare("bar")}).count();
add a comment |
up vote
0
down vote
I've created a simple Func for the case insensitive regex, which I use in my filter.
private Func<string, BsonRegularExpression> CaseInsensitiveCompare = (field) =>
BsonRegularExpression.Create(new Regex(field, RegexOptions.IgnoreCase));
Then you simply filter on a field as follows.
db.stuff.find({"foo": CaseInsensitiveCompare("bar")}).count();
add a comment |
up vote
0
down vote
up vote
0
down vote
I've created a simple Func for the case insensitive regex, which I use in my filter.
private Func<string, BsonRegularExpression> CaseInsensitiveCompare = (field) =>
BsonRegularExpression.Create(new Regex(field, RegexOptions.IgnoreCase));
Then you simply filter on a field as follows.
db.stuff.find({"foo": CaseInsensitiveCompare("bar")}).count();
I've created a simple Func for the case insensitive regex, which I use in my filter.
private Func<string, BsonRegularExpression> CaseInsensitiveCompare = (field) =>
BsonRegularExpression.Create(new Regex(field, RegexOptions.IgnoreCase));
Then you simply filter on a field as follows.
db.stuff.find({"foo": CaseInsensitiveCompare("bar")}).count();
answered Sep 5 '15 at 23:18
Nitesh
574513
574513
add a comment |
add a comment |
up vote
0
down vote
As you can see in mongo docs - since version 3.2 $text
index is case-insensitive by default: https://docs.mongodb.com/manual/core/index-text/#text-index-case-insensitivity
Create a text index and use $text operator in your query.
add a comment |
up vote
0
down vote
As you can see in mongo docs - since version 3.2 $text
index is case-insensitive by default: https://docs.mongodb.com/manual/core/index-text/#text-index-case-insensitivity
Create a text index and use $text operator in your query.
add a comment |
up vote
0
down vote
up vote
0
down vote
As you can see in mongo docs - since version 3.2 $text
index is case-insensitive by default: https://docs.mongodb.com/manual/core/index-text/#text-index-case-insensitivity
Create a text index and use $text operator in your query.
As you can see in mongo docs - since version 3.2 $text
index is case-insensitive by default: https://docs.mongodb.com/manual/core/index-text/#text-index-case-insensitivity
Create a text index and use $text operator in your query.
answered May 10 '17 at 15:40
avalanche1
758916
758916
add a comment |
add a comment |
up vote
0
down vote
These have been tested for string searches
{'_id': /.*CM.*/} ||find _id where _id contains ->CM
{'_id': /^CM/} ||find _id where _id starts ->CM
{'_id': /CM$/} ||find _id where _id ends ->CM
{'_id': /.*UcM075237.*/i} ||find _id where _id contains ->UcM075237, ignore upper/lower case
{'_id': /^UcM075237/i} ||find _id where _id starts ->UcM075237, ignore upper/lower case
{'_id': /UcM075237$/i} ||find _id where _id ends ->UcM075237, ignore upper/lower case
add a comment |
up vote
0
down vote
These have been tested for string searches
{'_id': /.*CM.*/} ||find _id where _id contains ->CM
{'_id': /^CM/} ||find _id where _id starts ->CM
{'_id': /CM$/} ||find _id where _id ends ->CM
{'_id': /.*UcM075237.*/i} ||find _id where _id contains ->UcM075237, ignore upper/lower case
{'_id': /^UcM075237/i} ||find _id where _id starts ->UcM075237, ignore upper/lower case
{'_id': /UcM075237$/i} ||find _id where _id ends ->UcM075237, ignore upper/lower case
add a comment |
up vote
0
down vote
up vote
0
down vote
These have been tested for string searches
{'_id': /.*CM.*/} ||find _id where _id contains ->CM
{'_id': /^CM/} ||find _id where _id starts ->CM
{'_id': /CM$/} ||find _id where _id ends ->CM
{'_id': /.*UcM075237.*/i} ||find _id where _id contains ->UcM075237, ignore upper/lower case
{'_id': /^UcM075237/i} ||find _id where _id starts ->UcM075237, ignore upper/lower case
{'_id': /UcM075237$/i} ||find _id where _id ends ->UcM075237, ignore upper/lower case
These have been tested for string searches
{'_id': /.*CM.*/} ||find _id where _id contains ->CM
{'_id': /^CM/} ||find _id where _id starts ->CM
{'_id': /CM$/} ||find _id where _id ends ->CM
{'_id': /.*UcM075237.*/i} ||find _id where _id contains ->UcM075237, ignore upper/lower case
{'_id': /^UcM075237/i} ||find _id where _id starts ->UcM075237, ignore upper/lower case
{'_id': /UcM075237$/i} ||find _id where _id ends ->UcM075237, ignore upper/lower case
answered May 12 '17 at 14:42
Ar maj
1,114914
1,114914
add a comment |
add a comment |
up vote
0
down vote
I had faced a similar issue and this is what worked for me:
const flavorExists = await Flavors.findOne({
'flavor.name': { $regex: flavorName, $options: 'i' },
});
add a comment |
up vote
0
down vote
I had faced a similar issue and this is what worked for me:
const flavorExists = await Flavors.findOne({
'flavor.name': { $regex: flavorName, $options: 'i' },
});
add a comment |
up vote
0
down vote
up vote
0
down vote
I had faced a similar issue and this is what worked for me:
const flavorExists = await Flavors.findOne({
'flavor.name': { $regex: flavorName, $options: 'i' },
});
I had faced a similar issue and this is what worked for me:
const flavorExists = await Flavors.findOne({
'flavor.name': { $regex: flavorName, $options: 'i' },
});
edited Apr 11 at 8:43
answered Apr 11 at 7:36
Woppi
2,27342957
2,27342957
add a comment |
add a comment |
up vote
0
down vote
Use RegExp,
In case if any other options do not work for you, RegExp is a good option. It makes the string case sensitive.
var username = "John";
var uname = new RegExp(username, "i");
The value of uname
will be like /John/i
.
use uname in queries instead of username,
and then its done.
I hope it will work for you too. All the Best.
add a comment |
up vote
0
down vote
Use RegExp,
In case if any other options do not work for you, RegExp is a good option. It makes the string case sensitive.
var username = "John";
var uname = new RegExp(username, "i");
The value of uname
will be like /John/i
.
use uname in queries instead of username,
and then its done.
I hope it will work for you too. All the Best.
add a comment |
up vote
0
down vote
up vote
0
down vote
Use RegExp,
In case if any other options do not work for you, RegExp is a good option. It makes the string case sensitive.
var username = "John";
var uname = new RegExp(username, "i");
The value of uname
will be like /John/i
.
use uname in queries instead of username,
and then its done.
I hope it will work for you too. All the Best.
Use RegExp,
In case if any other options do not work for you, RegExp is a good option. It makes the string case sensitive.
var username = "John";
var uname = new RegExp(username, "i");
The value of uname
will be like /John/i
.
use uname in queries instead of username,
and then its done.
I hope it will work for you too. All the Best.
edited Jul 3 at 5:21
rockyBalboa
2,14442033
2,14442033
answered Jul 3 at 5:06
Gouri Shankar
12
12
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f1863399%2fmongodb-is-it-possible-to-make-a-case-insensitive-query%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
3
Since MongoDB 3.2 you can execute case-insensitive search with
$caseSensitive: false
. See: docs.mongodb.org/manual/reference/operator/query/text/…– martin
Apr 4 '16 at 8:21
4
Note that that is on text indexes only.
– Willem D'Haeseleer
Aug 16 '16 at 15:40