Unique index in mongoDB 3.2 ignoring null values

29
votes

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?

5
mongodbindexingdatabase
Does the null value have any special meaning in your case? If not, you can always $unset the field for all null values which makes it possible to use the $exist operator. - joao
Adding unique index auto adds the null field so we can't have documents without having unique indexed key field. - Nikhil

5 Answers

52
votes

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.

6
votes

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.

5
votes

You can create partial index in mongo:3.2. Example, if ipaddress can be "", but "127.0.0.1" should be unique. The solution can be like this:

db.collectionName.createIndex(
 {"ipaddress":1},
 {"unique":true, "partialIndexExpression":{"ipaddress":{"$gt":""}}})

This will ignore "" values in ipaddress filed and still be unique

4
votes

{ "YourField" : { "$exists" : true, "$gt" : "0", "$type" : "string" } }

0
votes

To create at mongodbCompass you must write it as JSON:

for find other types wich supports see this link.

enter image description here