Trying to create composite unique index with partialFilterExpression

Question

I'm trying to create an unique index for my collection with composite keys, but some documents might have null on the fields of the index. Going by the documentation it seems I should be able to do it with partialFilterExpression.

I tried it as following:

db.collection.createIndex( 
 { a: 1, b: 1, c: 1 },
 { 
   "background":true, 
   "unique": true, 
   "partialFilterExpression": { 
     "a": { "$exists": true }, 
     "b": { "$exists": true }, 
     "c": { "$exists": true } 
   } 
 })

but that gave me the following error:

exception: E11000 duplicate key error collection: schema.collection index: a_1_b_1_c_1 dup key: { : null, : null, : null }

I even tried changing the partial filter criteria to this:

db.collection.createIndex(
  { a: 1, b: 1, c: 1 },
  { 
    "background":true,
    "unique": true,
    "partialFilterExpression": { 
      "a": { "$exists": true, "$ne": null  },
      "b": { "$exists": true, "$ne": null  },
      "c": { "$exists": true, "$ne": null  }
    } 
  }
)

But still returned the same error.

Did I misunderstand the use or am using it wrongly?

Furthermore you are trying to 're-invent the wheel". MongoDB has a "sparse indexes" option, which does what you want already db.collection.createIndex({ a: 1, b: 1, c: 1 },{ "background": true, "unique": true, "sparse": true }). Though as noted in the documentation you can "do more" with the newer option, the default behavior fits this specific case. — Neil Lunn
Actually, it doesn't suit my case. I have some documents in which a is present, but b or c aren't. In that case when I try to create the sparse index it fails with duplicate index {a: something, b: null, c: null}. That's why I went for that specific feature, because I needed the validation only when all three were present. — Felipe Gomes

Felipe Gomes Felipe Gomes · Accepted Answer · 2017-06-14T18:56:19

Just figured out what was my issue. The version of my monogo is 3.0.12 and partialFilterExpression was introduced in version 3.2

Trying to create composite unique index with partialFilterExpression

1 Answers