Unique index in mongoDB 3.2 ignoring null values

[Nikhil Jain]

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 index add 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?

[Stephen Steneker]

On Thursday, 3 March 2016 05:52:56 UTC+11, Nikhil Jain wrote:

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 index add the null value to the index key field and hence the documents can’t be ignored based on $exist criteria in the PartialFilterExpression.

Hi Nikhil,

A partial index in MongoDB 3.2+ only indexes documents that match the partialFilterExpression. Partial index functionality is a superset of a sparse index (see: Comparison with the sparse index). You can create a partial index with a unique constraint, but the uniqueness will only apply to documents that meet the partial index filter criteria.

Can you provide an example of the partial index you are trying to ensure as well as some sample documents? It would also be helpful to include any error message you are receiving as well as the specific version of MongoDB 3.2.x you are using.

Thanks,
Stephen

​

[Nikhil Jain]

Hi Stephen,

Here is the exact scenario.

I have a user collection that already has 50 records in it.  Now when I try to add the new key "user_name" to the collection with unique constraints, the mongoDB auto adds the data in the 50 records for the unique key fields. 

It tries to add the null values to all the records as uniquely indexed fields can't be left out. null value gets added to the first record but it fails on the 2nd one as it throws unique constraint error and due to this the index is not getting applied to the collection.

Let me know if I am not clear here.

[Stephen Steneker]

On Saturday, 5 March 2016 15:45:04 UTC+11, Nikhil Jain wrote:

I have a user collection that already has 50 records in it. Now when I try to add the new key “user_name” to the collection with unique constraints, the mongoDB auto adds the data in the 50 records for the unique key fields.

It tries to add the null values to all the records as uniquely indexed fields can’t be left out. null value gets added to the first record but it fails on the 2nd one as it throws unique constraint error and due to this the index is not getting applied to the collection.

Let me know if I am not clear here.

Hi Nikhil,

Steps to reproduce your issue in the mongo shell would definitely be clearer than a descriptive interpretation of what appears to be happening :).

Can you please provide:

• the exact syntax/commands you are trying to use to create the new index

• any error messages you receive when trying to create the index

• your MongoDB server version (i.e. as reported by db.version() in the mongo shell)?

Thanks,
Stephen

​

[Nikhil Jain]

Hi Stephen,

It's working on my end. There was some issue in writing a partialFilterExpression and so it was throwing an issue while applying indexes.

Sorry to take your time.  All good now.

~ Nikhil