We have a collection where few documents got duplicated due to an issue in the code. They are large in number (> 280K). Due to some user updates from the front end, these records have been updated. Now, these records cannot be removed as there is some transaction on these records. To avoid this, we are thinking of creating a unique index with a partial expression on the created date. However, this doesn't work. Need help on how to achieve this. As much as I could get in the documentation, a unique index with partial constraints will only consider the matching documents.
db.collection1.createIndex({"uniqueid":1},{"background":true, "unique":true, "partialFilterExperssion":{ "created_date":{"$gte":ISODate("2021-04-27T15:30:00Z")}}})
If you see here the created_date specified in the future as the time now is 2021-04-26T19:22:00, so ideally irrespective of the current data in the collection, based on the partialFilterExpression, this index should get created. However, this gives an error
{
"ok" : 0.0,
"errmsg" : "E11000 duplicate key error collection: lms.collection1 index: uniqueid_1 dup key: { : null }",
"code" : 11000
}
Why is this looking at existing data to create the index? Any help to understand the reason for the error will be appreciated here.
There is no issue with partial filter, partial filter is not considered as there is a spelling miss 'expression' part is misspelled. Once corrected, it worked like a charm. My bad.