Uploaded image for project: 'Core Server'
  1. Core Server
  2. SERVER-90

case insensitive index

    • Type: Icon: New Feature New Feature
    • Resolution: Done
    • Priority: Icon: Major - P3 Major - P3
    • 3.3.11
    • Affects Version/s: None
    • Component/s: Index Maintenance
    • None
    • Query
    • Fully Compatible

      Issue Status as of Aug 23, 2016

      ISSUE SUMMARY

      Case-insensitive indices are available in MongoDB 3.3.11 via the new collation feature. Collations allow queries to match and sort UTF-8 string data in a locale-aware fashion, but also allow users to configure case sensitivity. For general documentation of collation, see SERVER-1920 and Development Series 3.3.x Collation.

      TECHNICAL DETAILS

      Case-insensitivity can be specified as a collation with a strength of 2. This strength indicates that the collator should make case-insensitive comparisons. (Strength 1, the weakest comparison level, is both case-insensitive and diacritic-insensitive. Higher strengths are more discriminating whereas lower strengths are less discriminating.) Case-insensitivity, like any collation, can be specified at the level of an operation, an index, or as a collection default. Queries or index builds with no explicit collation will inherit the collection's default collation. An index is eligible for use by case-insensitive string matching or sorting operations if its case-insensitive collation matches that of the query.

      Example

      The following example shows how to create and query a case insensitive index with English case folding rules:

      > db.myCollection.createIndex({city: 1}, {collation: {locale: "en", strength: 2}});
      > db.myCollection.insert({_id: 1, city: "New York"});
      > db.myCollection.insert({_id: 2, city: "new york"});
      > db.myCollection.find({city: "new york"}).collation({locale: "en", strength: 2});
      { "_id" : 1, "city" : "New York" }
      { "_id" : 2, "city" : "new york" }
      

      IMPACT ON DOWNGRADE

      Downgrade from 3.4 to 3.2 is illegal if the data files contain any collections with a case-insensitive default collation or any case-insensitive indices. Before downgrading, all collections and indices with an associated collation must be dropped.

      FURTHER INFORMATION

      Documentation for this feature is available in the 3.3.x development series release notes. To join our beta program for Case Insensitive Indices, and suggest improvements to our implementation, please email beta@mongodb.com.

      Original description

      potential syntax:

      db.foo.ensureIndex( { name : 1 } , { caseInsensitive : true } )
      db.foo.ensureIndex( { name : 1 } , { caseInsensitive : true , locale : "FR" } )
      db.foo.ensureIndex( { name : 1 } , { caseInsensitive : true , localeKey : "user.country" } )
      
      db.foo.ensureIndex( { name : 1 } , { caseInsensitive : [ "name" ] } )
      

      reminder, you can aways do this for now:

      { name : { real : "Eliot" , sort : "eliot" } }
      ensureIndex( { "name.sort" : 1 } )
      

            Created:
            Updated:
            Resolved: