Unique index in mongoDB 3.2 ignoring null values

Question

I want to add a unique index to a field that ignores null values ​​in a unique indexed field and ignores documents that are filtered based on partialFilterExpression.

The problem is that Sparse indexes cannot be used with Partial index.

In addition, adding unique indexes adds a null value to the index key field, and therefore documents cannot be ignored based on the $ exist criteria in the PartialFilterExpression expression.

Is it possible to get around this situation in MongoDB 3.2?

Answer 1

I am adding this answer as I was looking for a solution and could not find it. This may not answer exactly this question, or maybe, but it will help many others like me.

Example. If a field with null is houseName and it is of type string , the solution could be this:

 db.collectionName.createIndex( {name: 1, houseName: 1}, {unique: true, partialFilterExpression: {houseName: {$type: "string"}}} ); 

This will ignore the null values ​​in the houseName field and still be unique.

Answer 2

Yes, you can create a partial index in MongoDB 3.2

See https://docs.mongodb.org/manual/core/index-partial/#index-type-partial

MongoDB recommends using a partial index on a sparse index. I suggest you abandon your sparse index in favor of a partial index.