MongoDB Optional Unique Index

Question

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?

Indeed. This is intention and will solve lot of your problems. I'll suggest to upgrade database engine if possible. — Saleem
– Saleem, Commented Mar 3, 2016 at 11:34

Saleem · Accepted Answer · 2016-03-03 13:04:58Z

5

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.

Please see https://docs.mongodb.org/manual/core/index-unique/#unique-partial-indexes

edited Mar 3, 2016 at 13:04

answered Mar 3, 2016 at 11:33

Saleem

9,0582 gold badges22 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

benjiman Over a year ago

Yes I know, but unfortunately I have to use version 3.0.9. However, how would you do this using a partial index?

Saleem Over a year ago

Well in That case you have only one option which I see profesor79 posted above. Create compound index on all three fields.

benjiman Over a year ago

Could you edit your answer to give an example how to create a unique partial index in that case?

Saleem Over a year ago

sure, can you please update your question and post sample documents covering case you are trying to solve.

profesor79 Over a year ago

imho partial index will have the same weakness in this case - as there is a business logic needed to handle multiple annon ids and then create an real user id

|

profesor79 · Accepted Answer · 2016-03-03 11:27:44Z

1

index a,c - cannot be sparse as is unique.....

index b,c - cannot be sparse as is unique.....

what about index a,b,c ?

db.benjiman.insert( { userId: "some-string", anonymousId: "some-other-string", project: {"_oid": "56d06bb6d9f75035956fa7ba"} })

db.benjiman.insert( { userId: "some-string2", project: {"_oid": "56d06bb6d9f75035956fa7ba"} })

db.benjiman.insert( { anonymousId: "some-other-string2", project: {"_oid": "56d06bb6d9f75035956fa7ba"} })

db.benjiman.createIndex({ "userId": 1, "anonymousId": 1, "project": 1 }, { unique: true })

answered Mar 3, 2016 at 11:27

profesor79

9,5033 gold badges36 silver badges53 bronze badges

2 Comments

benjiman Over a year ago

I thought about this too, but the problem with this approach is that it allows to insert the same userId for example if there is a different anonymousId provided.

profesor79 Over a year ago

ok, have a bigger image now, let's chat here chat.stackoverflow.com/rooms/104903/mongo-queries-issues

Collectives™ on Stack Overflow

MongoDB Optional Unique Index

2 Answers 2

11 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

11 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related