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.

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

Answer 4

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

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.

Answer 6

In Mongoose:-

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

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

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

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/

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

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