Details

    • Type: New Feature
    • Status: Closed
    • Priority: Major - P3
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 3.3.11
    • Component/s: Indexing
    • Labels:
      None

      Description

      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 } )
      

        Issue Links

          Activity

          Hide
          betoneto.tbo@gmail.com Roberto Germano Vieweg Neto added a comment -

          Another good feature can also be pass a compare function for the sort method, like this:

          db.myEntity.find().sort(function(myEntityA, myEntityB) { 
             return myEntityA.field.toLowerCase().compareTo(myEntityB.field.toLowerCase()); 
          })
          

          Sometimes you need to sort little data, and you doesn't want/need to create an index to do that.

          Show
          betoneto.tbo@gmail.com Roberto Germano Vieweg Neto added a comment - Another good feature can also be pass a compare function for the sort method, like this: db.myEntity.find().sort( function (myEntityA, myEntityB) { return myEntityA.field.toLowerCase().compareTo(myEntityB.field.toLowerCase()); }) Sometimes you need to sort little data, and you doesn't want/need to create an index to do that.
          Hide
          jesperbendtsen83@gmail.com Jesper Erik Bendtsen added a comment -

          Really hope this feature comes in the near future, everyone are used to search in case insensitive way, it really creates headache for developers!
          Meanwhile, I started to save strings with certain style, if I know the field is like that, rather than to create two fields, one for search/sort and one for display who can make the doc huge.

          Example in PHP
          strToUpper () - Make a string uppercase
          strtolower () - Make a string lowercase
          ucfirst () - Make a string's first character uppercase
          ucwords () - Uppercase the first character of each word in a string
          none = Slow search

          So when users save data I change it to one of the above. And when users searching I change the input to above and then do the search/sort.
          If I do not know if the field has a certain style. I use REGEX like this "new MongoDB \ BSON \ Regex ('^'. $ Value. '$', 'In')" which is slow. I really waiting for MongoDB will support case insensitive search...

          Know the above is not super, but maybe it will help someone until case insensitive search comes, really hopes it comes!? This ticket is from 2009, it worries me! Please reply with future plans?

          Show
          jesperbendtsen83@gmail.com Jesper Erik Bendtsen added a comment - Really hope this feature comes in the near future, everyone are used to search in case insensitive way, it really creates headache for developers! Meanwhile, I started to save strings with certain style, if I know the field is like that, rather than to create two fields, one for search/sort and one for display who can make the doc huge. Example in PHP strToUpper () - Make a string uppercase strtolower () - Make a string lowercase ucfirst () - Make a string's first character uppercase ucwords () - Uppercase the first character of each word in a string none = Slow search So when users save data I change it to one of the above. And when users searching I change the input to above and then do the search/sort. If I do not know if the field has a certain style. I use REGEX like this "new MongoDB \ BSON \ Regex ('^'. $ Value. '$', 'In')" which is slow. I really waiting for MongoDB will support case insensitive search... Know the above is not super, but maybe it will help someone until case insensitive search comes, really hopes it comes!? This ticket is from 2009, it worries me! Please reply with future plans?
          Hide
          oleg@tmsprintsystems.com Oleg Vivtash added a comment -

          Is there any update on when to expect this feature?
          It gets pretty messy to double sortable string fields amount when you have tens of them.

          Show
          oleg@tmsprintsystems.com Oleg Vivtash added a comment - Is there any update on when to expect this feature? It gets pretty messy to double sortable string fields amount when you have tens of them.
          Hide
          pasette Dan Pasette added a comment -

          Work on this feature is close to completion and scheduled to ship with 3.4. Once it's ready in a development release we will provide documentation and we'd love for you to give it a try.

          Show
          pasette Dan Pasette added a comment - Work on this feature is close to completion and scheduled to ship with 3.4. Once it's ready in a development release we will provide documentation and we'd love for you to give it a try.
          Hide
          Rahul Matte Rahul B. Mattte added a comment - - edited

          I am using below query to fetch the data in alphabetically sorted format

          collection.col.find(

          {name: somename}

          ).sort(

          {name: 1}

          ).toArray(function(error, namelist)

          {...}

          )

          The query returns the sorted data but in case sensitive format.

          { "name" : "AAA" }

          ,

          { "name" : "BBB" }

          ,

          { "name" : "aaa" }

          Can you please suggest any solution to get the result in insensitive format like below

          { "name" : "aaa" }

          ,

          { "name" : "AAA" }

          ,

          { "name" : "BBB" }

          Is this reported bug applicable for my issue also..please help me I am new to the mongoDB

          Show
          Rahul Matte Rahul B. Mattte added a comment - - edited I am using below query to fetch the data in alphabetically sorted format collection.col.find( {name: somename} ).sort( {name: 1} ).toArray(function(error, namelist) {...} ) The query returns the sorted data but in case sensitive format. { "name" : "AAA" } , { "name" : "BBB" } , { "name" : "aaa" } Can you please suggest any solution to get the result in insensitive format like below { "name" : "aaa" } , { "name" : "AAA" } , { "name" : "BBB" } Is this reported bug applicable for my issue also..please help me I am new to the mongoDB

            Dates

            • Created:
              Updated:
              Resolved:
              Days since reply:
              17 weeks, 6 days ago
              Date of 1st Reply: