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

Posted on

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

   { 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 :

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}

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

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

  { rss_id: 1},
    unique: true, 
    partialFilterExpression: {
      rss_id: {
        $type: [

Leave a Reply

Your email address will not be published. Required fields are marked *