Unique index in mongoDB 3.2 ignoring null values

Question

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

The problem is Sparse indexes can't be used with the Partial index.

Also, adding unique indexes, adds the null value to the index key field and hence the documents can't be ignored based on $exist criteria in the PartialFilterExpression.

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

Answer 1

I am adding this answer as I was looking for a solution and didn't find one. This may not answer exactly this question or may be, but will help lot of others out there like me.

Example. If the field with null is houseName and it is of type string, the solution can be like this

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

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

Answer 2

Yes, you can create partial index in MongoDB 3.2

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

MongoDB recommend usage of partial index over sparse index. I'll suggest you to drop your sparse index in favor of partial index.