MongoDB find does not follow collation rule

Question

I wanted to create an index on a membershipAccount collection, that had a collation rule set that would allow the find method to find a username while being case-insensitive.

[
    2,
    {
        "username" : 1.0
    },
    "username_1",
    "kollecto_prod.membershipAccounts",
    {
        "locale" : "da",
        "caseLevel" : false,
        "caseFirst" : "off",
        "strength" : 1,
        "numericOrdering" : false,
        "alternate" : "non-ignorable",
        "maxVariable" : "punct",
        "normalization" : false,
        "backwards" : false,
        "version" : "57.1"
    }
]

I have a user named - user@test.com in my membershipAccountscollection. But this is my result of my find queries:

db.getCollection('membershipAccounts').find({username: "user@test.com"}) // 1 result - ok
db.getCollection('membershipAccounts').find({username: "USER@test.com"}) // 0 results - nope
db.getCollection('membershipAccounts').find({username: "USER@TEST.com"}) // 0 results - nope
db.getCollection('membershipAccounts').find({username: "USER@TEST.COM"}) // 0 results - nope

Is my collation incorect or have I misunterstood the concept?

Answer 1

You need to either set the desired collation in the query, or set the default collation on the collection. Creating an index with a collation does not make that collation the default one used in queries.

Per query:

MongoDB Enterprise ruby-driver-rs:PRIMARY> db.foo.insert({v:'test'})
WriteResult({ "nInserted" : 1 })
MongoDB Enterprise ruby-driver-rs:PRIMARY> db.foo.find({v:'TEST'}).collation({locale:'en',caseLevel:false,strength:1})
{ "_id" : ObjectId("5ed8302ba6de3bc31c771682"), "v" : "test" }

Default collation:

MongoDB Enterprise ruby-driver-rs:PRIMARY> c={locale:'en',caseLevel:false,strength:1}
{ "locale" : "en", "caseLevel" : false, "strength" : 1 }
MongoDB Enterprise ruby-driver-rs:PRIMARY> db.createCollection('bar',{collation:c})
{
        "ok" : 1,
        "$clusterTime" : {
                "clusterTime" : Timestamp(1591226649, 1),
                "signature" : {
                        "hash" : BinData(0,"AAAAAAAAAAAAAAAAAAAAAAAAAAA="),
                        "keyId" : NumberLong(0)
                }
        },
        "operationTime" : Timestamp(1591226649, 1)
}
MongoDB Enterprise ruby-driver-rs:PRIMARY> db.bar.insert({v:'test'})
WriteResult({ "nInserted" : 1 })
MongoDB Enterprise ruby-driver-rs:PRIMARY> db.bar.find({v:'TEST'})
{ "_id" : ObjectId("5ed8311fa6de3bc31c771683"), "v" : "test" }