Case insensitive sorting in MongoDB

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

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.

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.

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

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

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.

In Mongoose:-

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

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

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

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/

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;
    }
    if (nameA > nameB) {
      return 1;
    }
    return 0;
  });

  return results;
}