Trouble creating a case-insensitive index for a non mandatory email field

Working with Data
1

Good day everyone, I’m facing a problem I hope you guys can help me solve, I have a User collection that has 2 kinds of identifiers, one is email, that users use to connect to our platform and the other is a uniqueid, used for internal purposes, we had a problem of users with duplicated emails because the case was different, we have a unique index for this but it doesn’t guard against the same email with a different case, so I’m trying to implement a unique case insensitive index, so we don’t have this problem anymore, the issue is that for some reason the DB doesn’t let me create the index this is the command I’m using

db.getCollection('users').createIndex({ email: 1 },{ unique: true, name: "case_insensitive_email", partialFilterExpression: { email: {$exists: true} }, collation: { locale: "en", strength: 1 } })

but when I try to create the index I get this error
E11000 duplicate key error collection: userDb.users index: case_insensitive_email collation: { locale: "en", caseLevel: false, caseFirst: "off", strength: 4, numericOrdering: false, alternate: "non-ignorable", maxVariable: "punct", normalization: false, backwards: false, version: "57.1" } dup key: { email: "0x3b552943351216140a7a512d4b08312f510114011401" }

I’ve checked the email field for duplicates and haven’t found anything, the dup key the error returns doesn’t seem to be in the DB and I haven’t found relevant information about this specific error, if I don’t add the collation option I can create the index but then I’ll still have the issue about email with different case added, can’t work it out in the application layer because a lot of different systems can connect to the DB.
I hope you can help me and thank you for your time.

2

If I understand correctly what you wrote, you DO NOT HAVE any document in the collection userDb.users with

{ email: "0x3b552943351216140a7a512d4b08312f510114011401" }

It is strange because it really looks like this is what the error message indicate.

It is just a hunch, but if you already have an index on email you might want to drop it before trying to create another one with different options. But I guess that you would get a different error message if that was the case. But the new index name will be email_1, which will be a duplicate of an existing index on email.

3

hey, thanks for your reply I do have documents in the collection, a lot of them in fact, but I’ve searched through all of them to find any duplicate and I can’t find any, that’s why I don’t understand why I can’t create the index.

4

I also deleted every other index that had email as a key and even then I couldn’t create it.

6

Hey, any update on this issue?

I tried mongodumping a collection, editing the metadata to use a collation, then mongorestoreing it, but it says : “error creating collection DB.users: error running create command: (BadValue) ‘idIndex’ must have the same collation as the collection.”. I even tried deleting the other indexes like email_1