CODEWITHSUNDEEP
mongodbindexingmongoose
Luis Sieira
Luis Sieira

Reputation: 31522

Mongo sparse index is not working as I expected

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

Upvotes: 4

Views: 2348

Answers (2)

Luis Sieira
Luis Sieira

Reputation: 31522

According to the unique index documentation for missing fields

Unique Index and Missing Field

If a document does not have a value for a field, the index entry for that item will be null in any index that includes it. Thus, in many situations you will want to combine the unique constraint with the sparse option. Sparse indexes skip over any document that is missing the indexed field, rather than storing null for the index entry. Since unique indexes cannot have duplicate values for a field, without the sparse option, MongoDB will reject the second document and all subsequent documents without the indexed field. Consider the following prototype.

Therefore, it seems legitimate to think that this will also work on compound indexes.

This was reported as a bug on jira.

MongoDB developers decided not to include this functionality and closed the request

It makes more sense to exclude documents from the index if ALL fields in the index are missing. Compound indexes also serve queries on the first, first+second, etc fields in the index, and so an index on a,b,c should be able to find all the documents where a=1, not only the ones where b and/or c also have values. This is more intuitive, and should be the default behavior.

Although some suggestions were made in an effort to define a proper semantics to differentiate the two possible cases

{sparse : true, sparseIfAnyValueMissing : true}

It could be useful not only for what I describe in the question, but also for document inheritance and support partial indexing

I have the situation where one of my columns is null when I first create it, but may get set to an ID later. And when it's set to an ID it needs to be unique with another column.

Unfortunately I can't enforce this using a unique index because it will fail since many rows may have null in one of the columns. If I were using a regular RDBMS with a sparse unique multi-column index, this would work fine. Unfortunately Mongo has chosen to work in a different way from all of the RDBMS' out there and cannot support this scenario.

Given that partial indexes are not a quick thing to add and don't seem like they will be added anytime soon, why is this issue closed? Please reopen and consider implementing this issue.

Unfortunately, it is not possible yet (I hope it will be at some point)

Upvotes: 0

JohnnyHK
JohnnyHK

Reputation: 311855

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.

Upvotes: 7

Related Questions