I have a MongoDB schema for users that looks something like this:
{
userId: "some-string",
anonymousId: "some-other-string",
project: {"$oid": "56d06bb6d9f75035956fa7ba"}
}
Users must have either a userId or an anonymousId. As users belong to a project, the model also has a reference called project, which links to the project collection.
Any userId or anonymousId value has to be unique per project, so I created two compound indexes as follows:
db.users.createIndex({ "userId": 1, "project": 1 }, { unique: true })
db.users.createIndex({ "anonymousId": 1, "project": 1 }, { unique: true })
However as not both userId and anonymousId have to be provided but just either one of them, MongoDB throws a duplicate key error for null values (for example if there is a second user with a provided anonymousId but no userId).
I therefore tried to add a sparse: true flag to the compound indexes, but this obviously only works if both fields are empty. I also tried adding the sparse flag only to the fields and not the compound indexes, but this doesn't work either.
To give an example, let's say I have the following three users in the collection:
{ userId: "user1", anonymousId: null, project: {"$oid": "56d06bb6d9f75035956fa7ba"}}
{ userId: "user2", anonymousId: "anonym", project: {"$oid": "56d06bb6d9f75035956fa7ba"}}
{ userId: "user3", anonymousId: "random", project: {"$oid": "56d06bb6d9f75035956fa7ba"}}
The following should be possible:
I want to be able to insert another user {userId: "user4", anonymousId: null} for the same project (without getting a duplicate key error)
However if I try to insert another user with {userId: "user3"} or another user with {anonymousId: "random"} there should be a duplicate key error
How else can I achieve this?
解决方案
If you are using MongoDB 3.2, you can use unique partial index instead of sparse index.
Partial index is actually recommended over sparse index
Example
db.users.createIndex({ "userId": 1, "project": 1 },
{ unique: true, partialFilterExpression:{
userId: { $exists: true, $gt : { $type : 10 } } } })
db.users.createIndex({ "anonymousId": 1, "project": 1 },
{ unique: true, partialFilterExpression:{
anonymouseId: { $exists: true, $gt : { $type : 10 } } } })
In above example, Unique index will only be created when userId is present and doesn't contain null value. Same holds true to anonymousId too.