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.

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.

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.