OPERATIONAL DEFECT DATABASE
...
...
I have two collections where I like to run an aggregation pipeline with $merge. However, the target collection has a partial unique index. According documentation $merge also works with spare indexes, so I would assume a partial index should also work.
JIRAUSER1257066 commented on Wed, 29 Sep 2021 16:03:21 +0000: Hi wernfried.domscheit@sunrise.net, Thank you for following up. We no longer take feature suggestions for MongoDB on the SERVER project, but you may submit your ideas for improvements over on our UserVoice. Best, Edwin JIRAUSER1257089 commented on Wed, 29 Sep 2021 06:25:26 +0000: Hi Edwin Sorry, I did a typo. Of course, the aggregation pipeline must be this: db.source.aggregate([ { $addFields: { merge: 1 } }, { $merge: { into: "target", on: "id" } } ]) Anyway, maybe you consider to support partial indexes in future releases of MongoDB. For example one of these: db.source.aggregate([ { $addFields: { merge: 1 } }, { $merge: { into: "target", on: ["id", "merge"] } } ]) { $merge: { into: -or- { db: , coll: }, on: -or- [ , ...] -or- index: , // Optional let: , // Optional whenMatched: , // Optional whenNotMatched: // Optional } } db.source.aggregate([ { $addFields: { merge: 1 } }, { $merge: { into: "target", index: "key" } } ]) { $merge: { into: -or- { db: , coll: }, on: -or- [ , ...], // Optional partialFilterExpression: // Optional let: , // Optional whenMatched: , // Optional whenNotMatched: // Optional } } db.source.aggregate([ { $addFields: { merge: 1 } }, { $merge: { into: "target", on: "id", partialFilterExpression: { merge: { $exists: true } } } } ]) Kind Regards Wernfried JIRAUSER1257066 commented on Tue, 28 Sep 2021 21:18:51 +0000: Hi wernfried.domscheit@sunrise.net, Thanks for reporting this issue! Specifying "on" when using $merge requires a unique index with keys that correspond to the on identifier fields. When specifying a partial index, the unique constraint is only applicable to the documents that meet the filter expression. This unique constraint doesn't prevent documents from being inserted if it doesn't meet the filter criteria. This behavior works as designed. While this is made explicit in our source code, I think this can be clarified in our documentation, so I've filed DOCS-14827 to address this. Best, Edwin
db.source.insertOne( { id: 1, foo: 999 } ) db.target.insertMany([ { id: 1, foo: 123, merge: 0 }, { id: 2, foo: 456, merge: 0 }, { id: 1, foo: 789 } ]) db.runCommand( { createIndexes: "target", indexes: [{ name: "key", key: { id: 1 }, unique: true, partialFilterExpression: { merge: { $exists: true } } }] } ) db.source.aggregate([ { $addFields: { merge: 1 } }, { $merge: { into: "target", on: "a" } } ]) Error: command failed: { "ok" : 0, "errmsg" : "Cannot find index to verify that join fields will be unique", "code" : 51183, "codeName" : "Location51183" } : aggregate failed :
Click on a version to see all relevant bugs
MongoDB Integration
Learn more about where this data comes from
Bug Scrub Advisor
Streamline upgrades with automated vendor bug scrubs
BugZero Enterprise
Wish you caught this bug sooner? Get proactive today.
