MongoDB 4.0: Use sharding and query with different collations

Question

we are working with a sharded MongoDB 4.0 setup and would like to use the collation option for some of our current queries, so that certain searches are down case-insensitive instead case-sensitive.

Our shards are partitioned in different sectors - say sectors "S1", "S2" and "S3". The sharding was done a while ago without stating a collation.

We have a lot of different queries, most of which include the shard key. So that the queries will be run only against one specific shard.

db.data.find({"section": "S1", ...).explain()
{
    "queryPlanner" : {
        "mongosPlannerVersion" : 1,
        "winningPlan" : {
            "stage" : "SINGLE_SHARD",
            (...)

Executing the same query, but with a collation it will result in:

db.data.find({"section": "S1", ...).collation("locale": "en", "strength": 2).explain()
{
    "queryPlanner" : {
        "mongosPlannerVersion" : 1,
        "winningPlan" : {
            "stage" : "SHARD_MERGE",
            (...)

Because of the performance implications we don't want to make scatter-gather queries (SHARD_MERGE).

Is there a way to refine the query that is run against our MongoS so that is able to tell which shard to query, even though the collation of the query and the sharding do not match?

D. SM D. SM · Accepted Answer · 2021-02-18T15:45:38

Lowercase your input prior to insertion, lowercase the queries, perform the queries without specifying the collation.

If you need the original case you can preserve it in another field.

MongoDB 4.0: Use sharding and query with different collations

1 Answers