MongoDB unique partial index for an array of sub-documents

user_362738 0
Viewed 116

I have a collection of documents of the following form:

{
    "_id" : UUID("e8d8f0a0-d8e4-4234-b017-251e90ad5202"),
    "legalEntities" : [
        {
            "id" : UUID("5aa70699-e6c3-4aac-a860-0c0d44ea4ab3"),
            "dunsNumber" : "123456789"
        },
        {
            "id" : UUID("e8d8f0a0-d8e4-4234-b017-251e90ad5202"),
            "dunsNumber" : null
        }
    ],
    "name" : "Company X"
}

and I want the legalEntities.dunsNumber to be unique across all documents except for null values.

How can I achieve that in MongoDB?

(Tried with partial index on legalEntities.dunsNumber, but this approach doesn't seem to work).

mongodb indexing
1 Answers

There are limitations in the partial index filter as described in SERVER-17853, and A discussion for the same scenario ​in MongoDB developer Forum.

For your use case, it requires $elemMatch and $ne operators but that is not supported yet.

My suggestion is to not use an array or index for now until SERVER-17853 is resolved.

user_89871280
Related