[SERVER-60104] $merge not working with partial index Created: 21/Sep/21  Updated: 27/Oct/23  Resolved: 28/Sep/21

Status: Closed
Project: Core Server
Component/s: None
Affects Version/s: 4.4.8
Fix Version/s: None

Type: Bug Priority: Major - P3
Reporter: Wernfried Domscheit Assignee: Edwin Zhou
Resolution: Works as Designed Votes: 0
Labels: aggregation-framework, index, merge, partial
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Related
related to DOCS-14827 When specifying the "on" field when u... Closed
Operating System: ALL
Steps To Reproduce:

 

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 :

 

 

Participants:

 Description   

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.



 Comments   
Comment by Edwin Zhou [ 29/Sep/21 ]

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

Comment by Wernfried Domscheit [ 29/Sep/21 ]

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: <collection> -or- { db: <db>, coll: <collection> },
     on: <identifier field> -or- [ <identifier field1>, ...] -or- index: <name of unique index> ,  // Optional
     let: <variables>,                                         // Optional
     whenMatched: <replace|keepExisting|merge|fail|pipeline>,  // Optional
     whenNotMatched: <insert|discard|fail>                     // Optional
} }
db.source.aggregate([ 
   { $addFields: { merge: 1 } }, 
   { $merge: { into: "target", index: "key" } } 
]) 
 
 
{ $merge: {
     into: <collection> -or- { db: <db>, coll: <collection> },
     on: <identifier field> -or- [ <identifier field1>, ...],  // Optional
     partialFilterExpression: <query expression for index>     // Optional
     let: <variables>,                                         // Optional
     whenMatched: <replace|keepExisting|merge|fail|pipeline>,  // Optional
     whenNotMatched: <insert|discard|fail>                     // Optional
} }
db.source.aggregate([ 
 { $addFields: { merge: 1 } }, 
 { $merge: { into: "target", on: "id", partialFilterExpression: { merge: { $exists: true } } } } 
]) 
 
 
 
 
 

 

Kind Regards
Wernfried

 

 

 

Comment by Edwin Zhou [ 28/Sep/21 ]

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

Generated at Thu Feb 08 05:48:58 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.