Case Insensitive Sorting in MongoDB

1. Overview

By default, the MongoDB engine considers character case when sorting extracted data. It’s possible to execute case insensitive sorting queries by specifying Aggregations or Collations.

In this short tutorial, we’ll look at the two solutions using both MongoDB Shell and Java.

2. Setting up an Environment

First of all, we need to run a MongoDB server. Let’s use a Docker image:

$ docker run -d -p 27017:27017 --name example-mongo mongo:latest

This will create a new temporary Docker container named “example-mongo” exposing port 27017. Now, we need to create a basic Mongo database with the data we need to test the solution.

First, let’s open a Mongo Shell inside the container:

$ docker exec -it example-mongo mongosh

Once we’re in the shell, let’s switch the context and enter the database named “sorting“:

> use sorting

Finally, let’s insert some data for us to try with our sort operations:

> db.users.insertMany([
  {name: "ben", surname: "ThisField" },
  {name: "aen", surname: "Does" },
  {name: "Aen", surname: "Not" },
  {name: "Ben", surname: "Matter" },
])

We’ve inserted similar values in some of the documents’ name fields. The only difference is the case of the first letter. At this point, the database is created and data inserted appropriately, so we’re ready for action.

3. Default Sorting

Let’s run the standard query without customization:

> db.getCollection('users').find({}).sort({name:1})

The data returned will be ordered considering the case. This means, for example, that the uppercase character “B” will be considered before the lowercase character “a”:

[
  {
    _id: ..., name: 'Aen', surname: 'Not'
  },
  {
    _id: ..., name: 'Ben', surname: 'Matter'
  },
  {
    _id: ..., name: 'aen', surname: 'Does'
  },
  {
    _id: ..., name: 'ben', surname: 'ThisField'
  }
]

Let’s now look at how we can make our sorts case-insensitive so that Ben and ben would appear together.

4. Case Insensitive Sorting in Mongo Shell

4.1. Sorting Using Collation

Let’s try using MongoDB Collation. Only available in MongoDB 3.4 and subsequent versions, it enables language-specific rules for string comparison.

The Collation ICU locale parameter drives how the database does sorting. Let’s use the “en” (English) locale:

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

This produces output where the names are clustered by letter:

[
  {
    _id: ..., name: 'aen', surname: 'Does'
  },
  {
    _id: ..., name: 'Aen', surname: 'Not'
  },
  {
    _id: ..., name: 'ben', surname: 'ThisField'
  },
  {
    _id: ..., name: 'Ben', surname: 'Matter'
  }
]

4.2. Sorting Using Aggregation

Let’s now use the Aggregation function:

> db.getCollection('users').aggregate([{
        "$project": {
            "name": 1,
            "surname": 1,
            "lowerName": {
                "$toLower": "$name"
            }
        }
    },
    {
        "$sort": {
            "lowerName": 1
        }
    }
])

Using the $project functionality, we add a lowerName field as the lowercase version of the name field. This allows us to sort using that field. It’ll give us a result object with an additional field, in the desired sort order:

[
  {
    _id: ..., name: 'aen', surname: 'Does', lowerName: 'aen'
  },
  {
    _id: ..., name: 'Aen', surname: 'Not', lowerName: 'aen'
  },
  {
    _id: ..., name: 'ben', surname: 'ThisField', lowerName: 'ben'
  },
  {
    _id: ..., name: 'Ben', surname: 'Matter', lowerName: 'ben'
  }
]

5. Case Insensitive Sorting with Java

Let’s try to implement the same methods in Java.

5.1. Configuration Boilerplate Code

Let’s first add the mongo-java-driver dependency:

<dependency>
    <groupId>org.mongodb</groupId>
    <artifactId>mongo-java-driver</artifactId>
    <version>3.12.10</version>
</dependency>

Then, let’s connect using the MongoClient:

MongoClient mongoClient = new MongoClient();
MongoDatabase db = mongoClient.getDatabase("sorting");
MongoCollection<Document> collection = db.getCollection("users");

5.2. Sorting Using Collation in Java

Let’s see how it’s possible to implement the “Collation” solution in Java:

FindIterable<Document> nameDoc = collection.find().sort(ascending("name"))
  .collation(Collation.builder().locale("en").build());

Here, we built the collation using the “en” locale. Then, we passed the created Collation object to the collation method of the FindIterable object.

Next, let’s read the results one by one using the MongoCursor:

MongoCursor cursor = nameDoc.cursor();
List expectedNamesOrdering = Arrays.asList("aen", "Aen", "ben", "Ben", "cen", "Cen");
List actualNamesOrdering = new ArrayList<>();
while (cursor.hasNext()) {
    Document document = cursor.next();
    actualNamesOrdering.add(document.get("name").toString());
}
assertEquals(expectedNamesOrdering, actualNamesOrdering);

5.3. Sorting Using Aggregations in Java

We can also sort the collection using Aggregation. Let’s recreate our command-line version using the Java API.

First, we rely on the project method to create a Bson object. This object will also include the lowerName field that is computed by transforming every character of the name into lowercase using the Projections class:

Bson projectBson = project(
  Projections.fields(
    Projections.include("name","surname"),
    Projections.computed("lowerName", Projections.computed("$toLower", "$name"))));

Next, we feed the aggregate method with a list containing the Bson of the previous snippet and the sort method:

AggregateIterable<Document> nameDoc = collection.aggregate(
  Arrays.asList(projectBson,
  sort(Sorts.ascending("lowerName"))));

In this case, as in the previous one, we could easily read the results using the MongoCursor.

6. Conclusion

In this article, we’ve seen how to perform a simple case-insensitive sorting of a MongoDB collection.

We used Aggregation and Collation methods in the MongoDB shell. In the end, we translated those queries and provided a simple Java implementation using the mongo-java-driver library.

As always, the full source code of the article is available over on GitHub.