Search code examples
mongodbmongodb-indexescompound-index

Unique compound index on array fields

I am trying to create mongo document with compound index. My sample doc looks like this

{ fname: "fname1", lname : "lname1", task : ["t11", "t12", "t13"] }

{ fname: "fname2", lname : "lname2", task : ["t21", "t22", "t23"] }

{ fname: "fname3", lname : "lname3", task : ["t31", "t32", "t33"] }

And the index in as below

createIndex({ fname: 1, lname: 1, task: 1 }, { unique: true, name: 'some-index-name'})

What i am expecting is

If any change in

  • fname OR
  • lname OR
  • task (any partial data change- at least one element )

should be considered as as unique document.

I am getting this exception "E11000 duplicate key error collection"

I looked into the fallowing links.But not able to figure it out.

What are the limitations of partial indexes?

https://docs.mongodb.com/manual/core/index-partial/

https://docs.mongodb.com/manual/indexes/#create-an-index

Mongo code base : https://github.com/mongodb/mongo/blob/69dec2fe8fed6d32ec4998ea7ec7ab063cb5b788/src/mongo/db/catalog/index_catalog.cpp#L422

Solution

  • You're correct that the restrictions of the unique index is as you described. However, that is only true for a singular value field. Once you use unique index on an array, that makes it a unique+multikey index.

    Basically, when you create an index on an array field, MongoDB creates an index entry for that document, for every array field in it. This is called a multikey index.

    For example, the document:

    {a:1, b:[1, 2, 3]}

    with the index:

    db.test.createIndex({a:1, b:1})

    will have three entries in the index, for each array element of b:

    {a:1, b:1}
{a:1, b:2}
{a:1, b:3}

    Therefore if you create a unique index on field a and b:

    db.test.createIndex({a:1, b:1}, {unique:true})

    all of these inserts will fail with unique index violation:

    > db.test.insert({a:1, b:1})
E11000 duplicate key error collection: test.test index: a_1_b_1 dup key: { : 1.0, : 1.0 }

> db.test.insert({a:1, b:2})
E11000 duplicate key error collection: test.test index: a_1_b_1 dup key: { : 1.0, : 2.0 }

> db.test.insert({a:1, b:3})
E11000 duplicate key error collection: test.test index: a_1_b_1 dup key: { : 1.0, : 3.0 }

> db.test.insert({a:1, b:[1]})
E11000 duplicate key error collection: test.test index: a_1_b_1 dup key: { : 1.0, : 1.0 }

> db.test.insert({a:1, b:[1,2]})
E11000 duplicate key error collection: test.test index: a_1_b_1 dup key: { : 1.0, : 1.0 }

> db.test.insert({a:1, b:[1,2,3]})
E11000 duplicate key error collection: test.test index: a_1_b_1 dup key: { : 1.0, : 1.0 }

    This is the reason why you cannot index more than one array field in an index. If you have multiple arrays, the number of index entries would be the multiplies of the length of the two arrays. Your index could easily be larger than your data, and it could take considerable time to scan such a large index.