Docs Menu

Docs HomeDevelop ApplicationsMongoDB Manual

Case Insensitive Indexes

On this page

  • Behavior
  • Examples
  • Create a Case Insensitive Index
  • Case Insensitive Indexes on Collections with a Default Collation

Case insensitive indexes support queries that perform string comparisons without regard for case.

You can create a case insensitive index with db.collection.createIndex() by specifying the collation parameter as an option. For example:

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

To specify a collation for a case sensitive index, include:

  • locale: specifies language rules. See Collation Locales for a list of available locales.

  • strength: determines comparison rules. A value of 1 or 2 indicates a case insensitive collation.

For additional collation fields, see Collation.

Using a case insensitive index does not affect the results of a query, but it can increase performance; see Indexes for a detailed discussion of the costs and benefits of indexes.

To use an index that specifies a collation, query and sort operations must specify the same collation as the index. If a collection has defined a collation, all queries and indexes inherit that collation unless they explicitly specify a different collation.

To use a case insensitive index on a collection with no default collation, create an index with a collation and set the strength parameter to 1 or 2 (see Collation for a detailed description of the strength parameter). You must specify the same collation at the query level in order to use the index-level collation.

The following example creates a collection with no default collation, then adds an index on the type field with a case insensitive collation.

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

To use the index, queries must specify the same collation.

db.fruit.insertMany( [
{ type: "apple" },
{ type: "Apple" },
{ type: "APPLE" }
] )
db.fruit.find( { type: "apple" } ) // does not use index, finds one result
db.fruit.find( { type: "apple" } ).collation( { locale: 'en', strength: 2 } )
// uses the index, finds three results
db.fruit.find( { type: "apple" } ).collation( { locale: 'en', strength: 1 } )
// does not use the index, finds three results

When you create a collection with a default collation, all the indexes you create subsequently inherit that collation unless you specify a different collation. All queries which do not specify a different collation also inherit the default collation.

The following example creates a collection called names with a default collation, 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 collection of names:

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

Queries on this collection use the specified collation by default, and if possible use the index as well.

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

The above operation uses the collection's default collation and finds all three documents. It uses the index on the first_name field for better performance.

It is still possible to perform case sensitive searches on this collection by specifying a different collation in the query:

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

The above operation finds only one document, because it uses a collation with no strength value specified. It does not use the collection's default collation or the index.

←  Partial IndexesHidden Indexes →