Case insensitive sorting in MongoDB

Question

How can I sort a MongoDB collection by a given field, case-insensitively? By default, I get A-Z before a-z.

Stowe

Answer 1

Update: As of now mongodb have case insensitive indexes:

Users.find({})
  .collation({locale: "en" })
  .sort({name: 1})
  .exec()
  .then(...)

shell:

db.getCollection('users')
  .find({})
  .collation({'locale':'en'})
  .sort({'firstName':1})

---

Update: This answer is out of date, 3.4 will have case insensitive indexes. Look to the JIRA for more information https://jira.mongodb.org/browse/SERVER-90

---

Unfortunately MongoDB does not yet have case insensitive indexes: https://jira.mongodb.org/browse/SERVER-90 and the task has been pushed back.

This means the only way to sort case insensitive currently is to actually create a specific "lower cased" field, copying the value (lower cased of course) of the sort field in question and sorting on that instead.

Boathouse

So there is no option for sorting the results with insensitive unless creating a new field with lower or upper cased values only. ryt?? – Stowe

@VarunKumar yep it is one of the bad points of MongoDB, I personally wouldn't use the below answer, MongoDB will be limited to 32meg of sort without the index resulting in very small result sets being allowed. – Boathouse

@VarunKumar to be more precise you will be limited to between sorting 2 to 32,000 records depending on the size of the documents, not only that but the sort will be completely in memory, which will be a killer – Boathouse

Holy *** it finally has! – Boathouse

@SSHThis can you please share the link where it is resolved? – Oria

@RahulMatte click on the JIRA link, it is resolved in the latest unstable, as are collations – Boathouse

Having said in the JIRA link, case insensitive index will come out with MongoDB v. 3.3.11 which basically you need to upgrade the mongodb for this feature. you can find new version here but as of today it is 3.2.10, the new version could be release in the future- mongodb.com/download-center#community – Kevel

This is resolved by MongoDB. Take a look at my answer bellow – Aigneis

@Boathouse is there some way I can use case insensitive index with reactive-mongo driver ? I am using scala with mongo. – Coulombe

@Coulombe unfortunately I do not program in Scala – Boathouse

Collations are supported by aggregate so I would say yes – Boathouse

@Coelho are you using collations? – Boathouse

@Coelho to be clear the reason why sort is case sensitive without collations ios because it uses lexical sorting without collations – Boathouse

update sorry, had wrong collation object passed - confirmed it works with aggregation – Coelho

Good answer. I just wanna add some documentation: docs.mongodb.com/manual/reference/collation – Rustler

Answer 2

Sorting does work like that in MongoDB but you can do this on the fly with aggregate:

Take the following data:

{ "field" : "BBB" }
{ "field" : "aaa" }
{ "field" : "AAA" }

So with the following statement:

db.collection.aggregate([
    { 
        "$project": {
            "field": 1,
            "insensitive": { "$toLower": "$field" }
        }
    },
    { "$sort": { "insensitive": 1 } }
])

Would produce results like:

{
    "field" : "aaa",
    "insensitive" : "aaa"
},
{
    "field" : "AAA",
    "insensitive" : "aaa"
},
{
    "field" : "BBB",
    "insensitive" : "bbb"
}

The actual order of insertion would be maintained for any values resulting in the same key when converted.

Sandstrom

Yup.. this looks gud.. but I want to implement the same from a java code.. So it'll more helpful if you share me that how this can be achieved from Java class with handling mongodb and query object. – Stowe

@VarunKumar You basically need to construct DBObject entries that you pass to the aggregate method. There is this example in the documentation resources. So it should not be hard to translate. And considering that it is an actual answer to show how it can be done that should not be too hard. – Sandstrom

Would this be slow (i.e. does the aggregate get evaluated every time?) – Nidianidicolous

Did anyone solve the issue using spring-data-mongo or Java? – Rex

This is good if you can't use collation. – Agapanthus

Answer 3

This has been an issue for quite a long time on MongoDB JIRA, but it is solved now. Take a look at this release notes for detailed documentation. You should use collation.

User.find()
    .collation({locale: "en" }) //or whatever collation you want
    .sort({name:1})
    .exec(function(err, users) {
        // use your case insensitive sorted results
    });

Aigneis

Answer 4

Adding the code .collation({'locale':'en'}) helped to solve my issue.

Vikki

But when I use collation in Mongoose with aggregation, I get an error MongooseError: Callback must be a function, got [object Object] – Vikki

Answer 5

As of now (mongodb 4), you can do the following:

mongo shell:

db.getCollection('users')
  .find({})
  .collation({'locale':'en'})
  .sort({'firstName':1});

mongoose:

Users.find({})
  .collation({locale: "en" })
  .sort({name: 1})
  .exec()
  .then(...)

Here are supported languages and locales by mongodb.

Kallick

Answer 6

In Mongoose:-

Customer.find()
  .collation({locale: "en" })
  .sort({comapany: 1})

Selfness

Answer 7

Here it is in Java. I mixed no-args and first key-val variants of BasicDBObject just for variety

        DBCollection coll = db.getCollection("foo");

        List<DBObject> pipe = new ArrayList<DBObject>();

        DBObject prjflds = new BasicDBObject();
        prjflds.put("field", 1);
        prjflds.put("insensitive", new BasicDBObject("$toLower", "$field"));

        DBObject project = new BasicDBObject();
        project.put("$project", prjflds);
        pipe.add(project);

        DBObject sort = new BasicDBObject();
        sort.put("$sort", new BasicDBObject("insensitive", 1));
        pipe.add(sort);

        AggregationOutput agg = coll.aggregate(pipe);

        for (DBObject result : agg.results()) {
            System.out.println(result);
        }

Unheard

Answer 8

If you want to sort and return all data in a document, you can add document: "$$ROOT"

db.collection.aggregate([
  { 
    $project: {
      field: 1,
      insensitive: { $toLower: "$field" },
      document: "$$ROOT"
    }
  },
  { $sort: { insensitive: 1 } }
]).toArray()

Calyptrogen

with $addFields you no longer need to add $$ROOT. – Burkett

Answer 9

Tried all the above and answers Consolidating the result

Answer-1:

db.collection.aggregate([
    { "$project": {
       "field": 1,
       "insensitive": { "$toLower": "$field" }
    }},
    { "$sort": { "insensitive": 1 } } ])

Aggregate query converts the field into lower, So performance is low for large data.

Answer-2:

db.collection.find({}).collation({locale: "en"}).sort({"name":1})

By default mongo follows uft-8 encoding(Z has high piriority then a) rules ,So overriding with language-specific rules. Its fast compare to above query Look into an official document to customize rules

https://docs.mongodb.com/manual/reference/collation/

Kindergartner

Answer 10

MongoDB with Panache provided

public <T extends Entity> ReactivePanacheQuery<T> withCollation(Collation collation); 

method in ReactivePanacheQuery class. Using this sorting the data with case-insensitive support can be achieved as follows.

repo.find(query, sort).withCollation(Collation.builder().locale("en").build())

Parathyroid

Answer 11

We solve this problem with the help of .sort function in JavaScript array

Here is the code

    function foo() {
      let results = collections.find({
        _id: _id
      }, {
        fields: {
          'username': 1,
        }
      }).fetch();

      results.sort((a, b)=>{
        var nameA = a.username.toUpperCase();
        var nameB = b.username.toUpperCase();

        if (nameA  nameB) {
          return 1;
        }
        return 0;
      });

      return results;
    }

Overexcite

Sorting in your app is a bad idea – Dachshund