Case Insensitive Indexes

New in version 3.4.

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

You can create a case insensitive index withdb.collection.createIndex() by specifying the collationparameter as an option. For example:

  1. db.collection.createIndex( { "key" : 1 },
  2.                            { collation: {
  3.                                locale : <locale>,
  4.                                strength : <strength>
  5.                              }
  6.                            } )

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

  • locale: specifies language rules. SeeCollation Locales for a list ofavailable locales.
  • strength: determines comparison rules. A value of1 or 2 indicates a case insensitive collation.

For additional collation fields, seeCollation.

Behavior

Using a case insensitive index does not affectthe results of a query, but it can increase performance; seeIndexes for a detailed discussion of the costs andbenefits of indexes.

To use an index that specifies a collation, query and sort operationsmust specify the same collation as the index. If a collection hasdefined a collation, all queries and indexes inherit that collationunless they explicitly specify a different collation.

Examples

Create a Case Insensitive Index

To use a case insensitive index on a collection with no defaultcollation, create an index with a collation and set the strengthparameter to 1 or 2 (seeCollation for a detaileddescription of the strength parameter). You must specify the samecollation 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 insensitivecollation.

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

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

  1. db.fruit.insert( [ { type: "apple" },
  2.                    { type: "Apple" },
  3.                    { type: "APPLE" } ] )
  4.  
  5. db.fruit.find( { type: "apple" } ) // does not use index, finds one result
  6.  
  7. db.fruit.find( { type: "apple" } ).collation( { locale: 'en', strength: 2 } )
  8. // uses the index, finds three results
  9.  
  10. db.fruit.find( { type: "apple" } ).collation( { locale: 'en', strength: 1 } )
  11. // does not use the index, finds three results

Case Insensitive Indexes on Collections with a Default Collation

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

The following example creates a collection called names with adefault collation, then creates an index on the first_name field.

  1. db.createCollection("names", { collation: { locale: 'en_US', strength: 2 } } )
  2.  
  3. db.names.createIndex( { first_name: 1 } ) // inherits the default collation

Insert a small collection of names:

  1. db.names.insert( [ { first_name: "Betsy" },
  2.                    { first_name: "BETSY"},
  3.                    { first_name: "betsy"} ] )

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

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

The above operation uses the collection’s default collation and findsall three documents. It uses the index on the first_name field forbetter performance.

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

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

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