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

indexmongodbmongodb-3.6

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

Best Answer

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