Building unique index on array of embedded documents

[Julien_AVON]

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.

[Prasad_Saya]

Hello @Julien_AVON, welcome to the MongoDB Community forum!

I want to be able to avoid duplicated dates in each documents

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.