Getting a case insensitive sort in MongoDB with collation

I've been using MongoDB find queries for several years, and I've had some issues with .skip() and .limit() in the past, but I only recently ran into this issue with sort(). When MongoDB wasn't returning my documents in the order I expected, I realised that it was because sort() was case-sensitive.

You can use sort in MongoDB to return documents in a sorted order. And if the field is a string, that means alphabetical order.

Sort is case-sensitive in MongoDB

But by default, sort() in MongoDB is case sensitive. And sometimes, this can be a problem.

Let me show you an example.

In our last MongoDB blog post, we were building an app for a car sales team. Let's continue that example, and imagine we want to sort our cars by brand.

Here's our car results grouped by makes:

[
  {
    "make": "BMW"
  },
  {
    "make": "Bentley"
  },
  {
    "make": "Audi"
  },
  {
    "make": "Ford"
  }
]

So, now we want to sort the cars alphabetically, we would expect:

• Audi
• Bentley
• BMW
• Ford

Let's run that query in MongoDB:

const results = await db.collection('cars').find({}).sort({ make: 1 });

// [
//   {
//     "make": "Audi"
//   },
//   {
//     "make": "BMW"
//   },
//   {
//     "make": "Bentley"
//   },
//   {
//     "make": "Ford"
//   }
// ]

💻 Try this sort in the Mongo Playground.

Ok, that's odd. BMW comes before Bentley, even though "E" is listed before "M". This is because MongoDB sort is case-sensitive. So that capital "M" is before the lowercase "e".

You might be ok with this. But personally, I feel that BMW should come after Bentley, alphabetically speaking.

To fix this, we need our sort to be case-insensitive. And luckily, it's not too hard to achieve. We will need to add a collation index.

Adding a collation index

To make .sort() case-insensitive in MongoDB, we need to create a case-insensitive index on our cars collection.

To do that, we will use collation with our index.

We will create the index for the field we want to sort, and add the collation option. In this case, I will use 'en' for the English language.

Let's create the index...

db.collection('cars').createIndex(
  { make: 1 },
  { collation: { locale: 'en' } }
);

Super!

Case-insensitive sort in MongoDB

Now that we have our new case-insensitive index, let's run that sort again:

const results = await db.collection('cars').find({}).sort({ make: 1 });

// [
//   {
//     "make": "Audi"
//   },
//   {
//     "make": "Bentley"
//   },
//   {
//     "make": "BMW"
//   },
//   {
//     "make": "Ford"
//   }
// ]

Nice! That's the order we were looking for!

You can add extra options to the collation index in addition to the locale option. You can specify the comparison with the strength option. I've left the strength option out of my index, so it defaults to level 3 comparison levels.

Related Posts:

• MongoDB skip and limit order is important in aggregation
• How to convert strings to ObjectIds in MongoDB
• Catching MongoDB E11000 duplicate key error in Node.js
• Using MongoDB project to change a field name (without aggregation)