Building unique index on array of embedded documents

Hello all,

I’m facing some difficulties to create a unique index:

Example collection with documents:
{"_id":1,“name”:service1,“results”:{“data”:[{“date”:“2021-05-01”,“result”:10},{“date”:“2021-05-02”,“result”:20}]}},
{"_id":2,“name”:service2,“results”:{“data”:[{“date”:“2021-05-01”,“result”:50},{“date”:“2021-05-02”,“result”:40}]}}

I want to be able to avoid duplicated dates in each documents. I tried to create a unique index but it doesn’t work.

I tried to create some index:
1 - {‘results.data.date’: 1}, {‘unique’: true}
I understand why it doesn’t work. There is no distinction between documents.

2 - {‘name’:1, ‘results.data.date’: 1}, {‘unique’: true}
Note: Name value is unique
But why this one doesn’t work.

For example, I was expecting that the following update will raise a duplicate error message. Because an existing index value should already exist with name:service1 && results.data.date: 2021-05-02.
That’s not the case. This update is successful.

{’_id’:1}, {"$set":{‘results.data’:{‘date’: ‘2021-05-02’, ‘result’: 22}

Final document:
{"_id":1,“name”:service1,“results”:{“data”:[{“date”:“2021-05-01”,“result”:10},{“date”:“2021-05-02”,“result”:20},{“date”:“2021-05-02”,“result”:22}]}}

What’s wrong with my understanding ?
How to create correctly the index for this feature?

Thanks for your help.

Hello @Julien_AVON, welcome to the MongoDB Community forum!

Indexes on array fields are called as Multikey Indexes. The unique index with array fields is only possible across documents - not within the same document.

See note on Unique Multikey Index in MongoDB Manual:

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.

2 Likes

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.