Merging two collections into one help

hi have two collections from two separate sources which i like to merge into one collection. both collections have one data entry called “name” that matches on both collections apart from that data is not the same.

i like to merger copy data from collection1 into collection2 with:

db.getCollection(“collection2”).aggregate([ {$merge:{ into:“collection1”, on:“name”, whenMatched: “replace”, whenNotMatched: “insert” }} ])

errors on:

“errmsg” : “Cannot find index to verify that join fields will be unique”,

database looks like this:

{ “_id” : ObjectId(“61643e7dbf5b6eee81501e11”), “name” : “example1”, “data1” : “example”}

{ “_id” : ObjectId(“61643e7dbf5b6e34545501345”), “name” : “example1”, “data1” : “example”}

i just want to match on “name” and then copy everything across that does not exist in collection2 from collection1 adding the extra data fields.

thanks for any tips on how to best to go about this thanks.

That tells me that you might need an index on the field name. I am not sure if you need it on collection1 or collection2 or both. Since you merge into collection1, I would start by making an index on this collection.

1 Like

Hi @Dudley_Morrown and welcome in the MongoDB Community :muscle: !

Yes, you need a unique index on the “name” field to make sure that each document in collection2 can only land on a single document in collection1.

It’s explained in the $merge doc in a few places. Especially this one.

To sum up, I think you need this:

db.collection1.createIndex( { name: 1 }, { unique: true } )

Cheers,
Maxime.

2 Likes

thank you @MaBeuLux88 and @steevej for your time and answers will get to it and try the recommendations out glad i got your support as i am complete noob at this!

1 Like