MongoDB does not allow to create unique partial index in field that contains null

Question

I'm using MongoDB 3.6.9 (with Ubuntu) to save a set of documents that contain one field named rss_id and for some documents, this field has the value null. I want to create a unique index in that field and tried this solution. From the documentation it seems a good solution but for some reason, I can't make it work in my MongoDB.

When I excute the folowing command (I'm using the shell of Robo 3T):

db.getCollection('noticias').createIndex(
 { rss_id: 1},
 { unique:true, partialFilterExpression: {rss_id: {$exists:true }}}
)

I get the error:

{
 "ok" : 0.0,
 "errmsg" : "E11000 duplicate key error collection: newsWall.noticias index: rss_id_1 dup key: { : null }",
 "code" : 11000,
 "codeName" : "DuplicateKey" }

• LowlyDBA - John M: Multiple NULLs are treated as duplicate values for a unique index.

Answer 1

So my theory is to create a partial index with a unique constraint with a filter expression such that the partial index will add an entry only if the type is not the type 'null'. I specify all types except null to make this happen. For a list of types see https://docs.mongodb.com/manual/reference/operator/query/type/#op._S_type

db.noticias.createIndex(
 { rss_id: 1},
 { 
 unique: true, 
 partialFilterExpression: {
 rss_id: {
 $type: [
 "double",
 "string",
 "object",
 "array",
 "binData",
 "undefined",
 "objectId",
 "bool",
 "date",
 "regex",
 "dbPointer",
 "javascript",
 "symbol",
 "javascriptWithScope",
 "int",
 "timestamp",
 "long",
 "decimal",
 "minKey",
 "maxKey"
 ]
 }
 }
 }
)

Answer 2

Please use the below query to create the unique partial index. The reason why your query work is, there may many fields with the value like {rss_id: null}

db.getCollection('noticias').createIndex(
 { rss_id: 1},
 { unique:true, partialFilterExpression: {rss_id: {$ne:null}}}
)

Or you can use sparse index option along with unique index as shown below

db.getCollection('noticias').createIndex( { rss_id: 1}, { sparse: true, unique: true })

• barrypicker: receiving error "errmsg" : "unsupported expression in partial index: $not\n rss_id $eq null\n",
• barrypicker: Sparse indexes look for existence of a field, not the value (if null or otherwise).
• Yuvaraj V: received "errmsg" : "unsupported expression in partial index: $not\n rss_id $eq null\n"