Merge collections in Mongodb 3.4.4

Question

Imagine that I've the following document in collection A:

{
  "_id" : 1,
  "ratio" : 6.0
}

I've also the following documents in collection B:

[
  {
    "_id" : 1,
    ratio : 3.0
  },
  {
    "_id" : 2,
    ratio : 4.0
  }
]

How can I merge them together to produce the following output:

[
  {
    "_id" : 1,
    ratio : 6.0
  },
  {
    "_id" : 2,
    ratio : 4.0
  }
]

So, as you can see above:

When collectionA._id matches to collectionB._id, it produces the ratio from collectionA as the result,
When ids don't match, it takes the ratio from collectionB.
collectionA will not have ids which doesn't exist in collectionB, because, I derive collectionB from collectionA in a prior aggregation.
And, for example, if there are more ids in collectionB, they will be added to the output as well.

So, basically, collectionA overwrites collectionB and then the unmatched docs from collectionB is added to the result, as well.

This is trivial to implement in my app code however there are millions of records exist in my database so I want to do it in MongoDB. I know also that there is no full-outer-join in MongoDB, is there a way to achieve the result?

This is trivial to implement by just reading both collections and merging them in the app. — Sergio Tulentsev, Jun 16 '17 at 14:30
Yeah, I know but there are millions of records, so I want to do it through MongoDB. — Inanc Gumus, Jun 16 '17 at 14:30
"when collectionA._id matches to collectionB._id, it produces the collectionB.ratio" - your output contradicts this assertion. And the next one. — Sergio Tulentsev, Jun 16 '17 at 14:30
"when collectionA._id matches to collectionB._id, it produces the collectionA.ratio, but, when it doesn't match, it produces collectionA.ratio." - soo, it's collectionA always? — Sergio Tulentsev, Jun 16 '17 at 14:33
Yeah, exactly, but, I also want to add the collectionB's docs if collectionA doesn't contain them. — Inanc Gumus, Jun 16 '17 at 14:34
If there are millions, maybe you could do this incrementally in bunches — Omri Luzon, Jun 16 '17 at 14:40
Yeah, but that won't be trivial, and I'll be doing it that way if there's no other solution. Keeping this in database is more logical because I declare that I want this and database will also optimize it in the future versions. However, my app will not. — Inanc Gumus, Jun 16 '17 at 14:43

Sergey Berezovskiy · Answer 1 · 2017-06-16T14:51:21.173

-1

So, basically, collectionA overwrites collectionB and then the unmatched docs from collectionB are added to the result, as well.

You can do only left joins with MongoDB, so you cannot select documents from collectionB which not match any document in collectionA when you query collectionA. Thus you should do it in two queries. First one simply selects documents from collectionA (these documents overwrite documents from collectionB anyway, so we are not interested in documents from collectionB wich match documents in collectionA).

And second query - get documents from collectionB which don't match any documents in collectionA:

db.collectionB.aggregate([
 {
    $lookup: {
       from: "collectionA",
       localField: "_id",
       foreignField: "_id",
       as: "docs"
    }       
 },
 { $match: { docs: {$eq: [] } } },
 { $project: { docs: 0 } }
])

edited Jun 16 '17 at 14:51

answered Jun 16 '17 at 14:45

Sergey Berezovskiy

232,247
41
429
459

Thx, so how to combine the both queries' result into a collection again? Like as the output that I needed in the question? [MapReduce](https://stackoverflow.com/questions/5681851/mongodb-combine-data-from-multiple-collections-into-one-how#8746805)? – Inanc Gumus Jun 16 '17 at 15:14
@inanc it depends on the way you want to use the data. What do you want to do with results? – Sergey Berezovskiy Jun 16 '17 at 15:37
I'll display the merged results on a webpage. – Inanc Gumus Jun 16 '17 at 15:59

Merge collections in Mongodb 3.4.4

1 Answers1