mongodb 对象唯一索引_MongoDB可选唯一索引

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.