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" }

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 })