CODEWITHSUNDEEP
mongodb
w43L
w43L

Reputation: 565

case-insensitive query on mongodb

is there a way to query for a case-insensitive value on mongo without using map/reduce?

Upvotes: 17

Views: 14455

Answers (6)

ray
ray

Reputation: 15276

With MongoDB v3.6+, you can chain up $toLower/$toUpper with $expr to perform case-insensitive search.

db.collection.find({
  $expr: {
    $eq: [
      {
        $toLower: "$value"
      },
      {
        $toLower: "apple"// your search string here
        
      }
    ]
  }
})

Here is the Mongo Playground for your reference.

Upvotes: 0

talha_ah
talha_ah

Reputation: 376

If there are some special characters in the query, regex simple will not work. You will need to escape those special characters.

The following helper function can help without installing any third-party library:

const escapeSpecialChars = (str) => {
  return str.replace(/[-[\]{}()*+?.,\\^$|#\s]/g, "\\$&");
}

And your query will be like this:

db.collection.find({ field: { $regex: escapeSpecialChars(query), $options: "i" }})

OR 

db.collection.find({ field: { $regex: `.*${escapeSpecialChars(query)}.*`, $options: "i" }})

Hope it will help!

Upvotes: 0

Shalabh Raizada
Shalabh Raizada

Reputation: 364

Use regular expressions matching as below. The 'i' shows case insensitivity.

var collections = mongoDatabase.GetCollection("Abcd");

var queryA = Query.And(
         Query.Matches("strName", new BsonRegularExpression("MSID", "i")), 
         Query.Matches("strVal", new BsonRegularExpression("154800", "i")));

var queryB = Query.And(
       Query.Matches("strName", new BsonRegularExpression("Operation","i")),
       Query.Matches("strVal", new BsonRegularExpression("8221", "i")));

var getA = collections.Find(queryA);
var getB = collections.Find(queryB);

Upvotes: 0

JayCrossler
JayCrossler

Reputation: 2129

When using it with Node.js, it's best to build a RegEx object in the query.

Room.findOne({'name': new RegExp(roomName, 'i')}, {}, function(err, room) {
...

Upvotes: 2

user2665694
user2665694

Reputation:

You have to normalize the data to be queried. Using a regular expression for case-insensitive search might work as well it won't use indexes. So your only option is to normalize. If you need to preserve the original state then you need to denormalize the data and store the normalized values in a dedicated column of the document.

Upvotes: 8

Andrew Orsich
Andrew Orsich

Reputation: 53695

Suppose you have document that contains tag field and you want search on it

Tags
{
  tag,
  ...
 }

First option is use regex(but it work slow as @RestRisiko said):

db.tags.find( { "tag" : { "$regex" : "C#", "$options" : "-i" } })

Second option is create another, lower case field( and in mongodb it best way):

Tags
{
  tag,
  tagLower,
  ..
}

And use find as usual:

db.tags.find( { "tagLower" : "c#"})

It will work faster, because above code can use index for search.

Upvotes: 25

Related Questions