JIYIK CN >

In this article, case insensitive queries are discussed briefly and in detail. Also, case insensitive search queries are explained in detail.

This article discusses the following topics.

1. Case-insensitive search
2. Improve case-insensitive regular expression queries
3. find()Use regular expressions for case-insensitive searches in the

Case insensitive query in MongoDB

Case-insensitive indexes support searches that compare strings without regard to case.

You db.collection.createIndex()can create a case-insensitive index by including the collation parameter as an optional parameter.

db.collection.createIndex( { "key" : 1 },
                           { collation: {
                               locale : <locale>,
                               strength : <strength>
                             }
                           } )

Include the following when specifying a collation for a case-sensitive index.

1. locale - specifies the language rules.
2. strength - used to determine the comparison rule. A value of 1 or 2 will indicate a case-insensitive sorting rule.

Behavior:

Using a case-insensitive index does not affect query results; however, it can improve speed.

To use an index specified by a collation, queries and sort operations must use the same collation as the index. If a collection defines a collation, all queries and indexes using the collection inherit it unless they specify a different collation.

Creating a case-insensitive index

To use a case-insensitive index on a collection that does not have a default collation, create an index with a collation and specify the strength option as 1 or 2. To use an index-level collation, you must provide the same collation at the query level.

The following example generates a collection with no default collation and adds an index with a case-insensitive collation to the type column.

db.createCollection("fruit")

db.fruit.createIndex( { type: 1},
                      { collation: { locale: `en`, strength: 2 } } )

The query must have the same collation to use the index.

db.fruit.insertMany( [
   { type: "bloctak" },
   { type: "Bloctak" },
   { type: "BLOCTAK" }
] )

db.fruit.find( { type: "bloctak" } )
//not use index, finds one result

db.fruit.find( { type: "bloctak" } ).collation( { locale: `en`, strength: 2 } )
// uses index, and will find three results

db.fruit.find( { type: "bloctak" } ).collation( { locale: `en`, strength: 1 } )
//not uses the index, finds three results

Case-Insensitive Indexes on Collections Using the Default Collation If you establish a collection using the default collation, all future indexes inherit that collation unless you provide a different collation. All queries that do not specify a collation inherit the default collation.

The following example generates a collection of names with the default collation and then creates an index on the first_name field.

db.createCollection("names", { collation: { locale: `en_US`, strength: 2 } } )

db.names.createIndex( { first_name: 1 } ) // inherits the default collation

Insert a small section of the name:

db.names.insertMany( [
   { first_name: "Betsy" },
   { first_name: "BETSY"},
   { first_name: "betsy"}
] )

By default, queries against this collection use the provided collation and, if possible, an index.

db.names.find( { first_name: "betsy" } )
// inherits the default collation: { collation: { locale: `en_US`, strength: 2 } }
// finds three results

The preceding procedure finds all three documents using the collection's default collation. It uses an index on the first_name field for efficiency.

The collection may still perform case-sensitive searches by specifying a different collation in the query.

db.names.find( { first_name: "betsy" } ).collation( { locale: `en_US` } )
// not use the collection`s default collation, finds one result

The preceding procedure returns only one document because it uses a collation for which no strength value is provided. It does not use the default collation for the index or collection.

Improve case-insensitive regular expression queries

If you frequently perform case-insensitive regular expression queries (using the I option), you should create a case-insensitive index to accommodate your searches.

A collation on an index can be used to provide language-specific string comparison rules, such as rules for letter case and accents. A case-insensitive index significantly improves the performance of case-insensitive queries.

Consider the following document in the employees collection. _idThis collection contains no indexes other than the usual index.

db={
  "employees": [
    {
      "_id": 1,
      "first_name": "Hannah",
      "last_name": "Simmons",
      "dept": "Engineering"
    },
    {
      "_id": 2,
      "first_name": "Michael",
      "last_name": "Hughes",
      "dept": "Security"
    },
    {
      "_id": 3,
      "first_name": "Wendy",
      "last_name": "Crawford",
      "dept": "Human Resources"
    },
    {
      "_id": 4,
      "first_name": "MICHAEL",
      "last_name": "FLORES",
      "dept": "Sales"
    }
  ]
}

If your application frequently searches the first_name column, you might want to use a case-insensitive regular expression query to discover matching names.

Case-insensitive regular expressions are also helpful in matching different data formats, such as the example above where your first_name is Michael and MICHAEL.

If the user searches for michael, the program might execute the following query.

db.employees.find({
  first_name: {
    $regex: "michael",
    $options: "i"
  }
})

Because this query contains $regex:

{ "_id" : 2, "first_name" : "Michael", "last_name" : "Hughes", "dept" : "Security" }
{ "_id" : 4, "first_name" : "MICHAEL", "last_name" : "FLORES", "dept" : "Sales" }

Although this query returns the desired documents, case-insensitive regular expression queries are slow without supported indexes. You can improve efficiency by creating a case-insensitive index on the first_name field.

db.employees.createIndex(
  { first_name: 1 },
  { collation: { locale: 'en', strength: 2 } }
)

When indexing collated documents with the strength field set to 1 or 2, the index is case-insensitive to allow for a broader interpretation of collated documents and various strength values.

For applications to use the index, you must also mention the index's collation document in your queries. Remove the $regex operator from the previous db.collection.find() function and use the newly constructed index instead.

db.employees.find( { first_name: "michael" } ).collation( { locale: 'en', strength: 2 } )

Do not use the $regex operator when using a case-insensitive index for a query. The $regex implementation does not support collations and cannot use case-insensitive indexes.

Use regular expressions in find() method to do case insensitive search in MongoDB

Use regular expressions in find()the method to perform a case-insensitive search.

grammar:

db.demo572.find({"yourFieldName" : { `$regex`:/^yourValue$/i}});

To understand the above syntax, let us create a document collection.

> db.demo572.insertOne({"CountryName":"US"});{
   "acknowledged" : true, "insertedId" : ObjectId("5e915f0e581e9acd78b427f1")
}
> db.demo572.insertOne({"CountryName":"UK"});{
   "acknowledged" : true, "insertedId" : ObjectId("5e915f17581e9acd78b427f2")
}
> db.demo572.insertOne({"CountryName":"Us"});{
   "acknowledged" : true, "insertedId" : ObjectId("5e915f1b581e9acd78b427f3")
}
> db.demo572.insertOne({"CountryName":"AUS"});{
   "acknowledged" : true, "insertedId" : ObjectId("5e915f20581e9acd78b427f4")
}
> db.demo572.insertOne({"CountryName":"us"});{
   "acknowledged" : true, "insertedId" : ObjectId("5e915f25581e9acd78b427f5")
}

find()The function displays all documents in a collection.

db.demo572.find();

This will produce the following output.

Following is the query for case insensitive search.

> db.demo572.find({"CountryName" : { `$regex`:/^US$/i}});

This will produce the following output.