MongoDB Case Insensitive Index Not Working

Question

I have a collection of users, and one of the document fields is named leaderboard_name. It's a text field, and I want to be able to query it in a case-insensitive manner.

So I created a new index with custom collation following the Mongo docs. I created this using the compass GUI, and set locale = en, strength = 1 and left everything else default:

I have the following document I want to query:

{
  leaderboard_name: "johndoe"
}

I expected with this index that I should be able to query for JOHNDOE and find this document, but I keep getting no results. Only when I search with matching case do I get a match.

Even the explain plan is saying no indexes were used in the query:

This is MongoDB 4.0.16, and my understanding is the case-insensitive collation support has been around since 3.4. I have looked at this answer, and aside from using compass to create the index instead of mongo shell, I think I have done everything else the same.

You have to specify the collation option _also_ in the query. — prasad_, Feb 27 '20 at 05:43
Please don't paste images. Use formatted text instead for the code. — Wernfried Domscheit, Feb 27 '20 at 07:23
@prasad_ Thanks! I assumed by only querying the `leaderboard_name` field that it would automatically pick the index I created. — Mike, Feb 27 '20 at 13:42

score 2 · Accepted Answer · answered Mar 04 '20 at 00:06

In case anyone else stumbles on this, the solution is to ensure the query includes collation details. In my case this meant changing my Java query to:

private final static Collation IGNORE_CASE =
        Collation.builder()
                .locale("en")
                .collationStrength(CollationStrength.PRIMARY)
                .build();

Document user =
        userCollection
                .find(new Document("leaderboard_name", "johndoe"))
                .collation(IGNORE_CASE)
                .first();

MongoDB Case Insensitive Index Not Working

1 Answers1