Mongo sparse index is not working as I expected

Question

I said 'as I expected', because I might be misunderstanding how it should work.

I have a model containing objects like this one :

{
    "_id" : ObjectId("56408d76ef82679937000008"),
    "_type" : "ford",
    "year" : 1986,
    "model" : "sierra",
    "model_unique" : 1,
    "__v" : 0
}

I need a compound unique index that will not allow to insert two objects with the same _type and model combination unless specified.

The way I thought I could specify that, was using the model_unique column and make the index sparse, so adding the former document twice should fail, whereas the following should be allowed (note that there is no model_unique field):

{
    "_id" : ObjectId("56408e0d636779c83700000a"),
    "_type" : "veridianDynamics",
    "year" : 1986,
    "model" : "sierra",
    "__v" : 0
}
{
    "_id" : ObjectId("another ID"),
    "_type" : "veridianDynamics",
    "year" : 2003,
    "model" : "sierra",
    "__v" : 0
}

I thought this would work with this index:

Schema.index({"_type": 1, "model": 1, "model_unique": 1}, { unique: true, sparse: true });

But it is actually failing with:

[MongoError: insertDocument :: caused by :: 11000 E11000 duplicate key error index: mongoose-schema-extend.vehicles.$_type_1_model_1_model_unique_1  dup key: { : "veridianDynamics", : "sierra", : null }]

So apparently it is considering that the undefined fields have a null value.

I'm using mongod --version db version v2.6.11

And npm -v mongoose 2.14.4

Answer 1

From the documentation on sparse compound indexes:

Sparse compound indexes that only contain ascending/descending index keys will index a document as long as the document contains at least one of the keys.

What this means in your case is that only when all three components of the compound index are missing from the document, will the document be excluded from the index, and thus exempt from the unique constraint.

So the sparse index you're trying to add would allow multiple docs without any of the three keys, but for all other cases, the combination of all three fields must be unique, with any missing fields getting a value of null.

In your example docs, they both would look like the following from the perspective of the unique index:

{
    "_type" : "veridianDynamics",
    "model" : "sierra",
    "model_unique : null
}

And thus, not unique.

FYI, there are exceptions to this rule where the existence of a geospatial or text index in your compound, sparse index changes the rules to only consider that specially indexed field when determining whether to include the document in the index.