2

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" }
1
  • 1
    Multiple NULLs are treated as duplicate values for a unique index. Feb 26 '19 at 16:11
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 })
3
  • 3
    receiving error "errmsg" : "unsupported expression in partial index: $not\n rss_id $eq null\n", Nov 8 '19 at 18:23
  • Sparse indexes look for existence of a field, not the value (if null or otherwise). Nov 8 '19 at 19:20
  • received "errmsg" : "unsupported expression in partial index: $not\n rss_id $eq null\n"
    – Yuvaraj V
    Mar 20 at 10:29
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.