MongoDB unique index for all array elements

Question

I'm trying to create unique index for array field in document. This index should works like when I have one document with array which contain two elements, then if I want to add a new document where array field if contain these two elements then should happen duplicate error - but not in situation when only one of elements is duplicated in another array. Maybe I'll show the example what I mean:

First I create a simple document:

{
    "name" : "Just a name",
    "users" : [ 
        "user1", 
        "user2"
    ]
}

And I want to create unique index on 'users' array field. The result of what I want is to make it possible to create another documents like this:

{
    "name" : "Just a name",
    "users" : [ 
        "user1", 
        "user3"
    ]
}

or

{
    "name" : "Just a name",
    "users" : [ 
        "user2", 
        "user5"
    ]
}

BUT it should be impossible to create second:

{
    "name" : "Just a name",
    "users" : [ 
        "user1", 
        "user2"
    ]
}

Or reversed:

{
    "name" : "Just a name",
    "users" : [ 
        "user2", 
        "user1"
    ]
}

But this is impossible because Mongo give me a error that "users1" is duplicated. Is it possible to create unique index on all array elements as shown above?

Helpful answers on this Question : https://stackoverflow.com/q/6743849/4050261 — Adarsh Madrecha, Aug 20 '21 at 08:29

score 4 · Answer 1 · answered Feb 04 '19 at 01:20

As per the Mongo official documentation

For unique indexes, the unique constraint applies across separate documents in the collection rather than within a single document.

Because the unique constraint applies to separate documents, for a unique multikey index, a document may have array elements that result in repeating index key values as long as the index key values for that document do not duplicate those of another document.

So you can't insert the second documents as

{
"name" : "Just a name",
"users" : [ 
    "user1", 
    "user3"
]
}

You will get the duplicate error of unique constraint:

> db.st1.insert({"name":"Just a name","users":["user1","user3"]})
WriteResult({
   "nInserted" : 0,
   "writeError" : {
      "code" : 11000,
      "errmsg" : "E11000 duplicate key error collection: test.st1 index: users_1 dup key: { : \"user1\" }"
   }
})

Since users user1 already exist the users index for the the fist documents.

Currently you have only solution to manage it through your code from where you are inserting into the collection. Before save or update make a validation logic and make sure the constraint you want to impose.

score 0 · Answer 2 · answered Apr 18 '19 at 19:36

I have a very similar problem and sadly it seems it's not possible. Not because unique constraint applies only across separate documents, but because:

To index a field that holds an array value, MongoDB creates an index key for each element in the array

i.e. each of the individual array elements has to be unique across all other documents.

MongoDB unique index for all array elements

2 Answers2