0

Hi I have 3 Collections which are being updated by 3 independent process; one collection will have Source = A, 2nd will have Source = B and third one will have Source = C. All Collections are of same Type and have same _Id Key Field (PolicyId+EndDate).

Now I want to create new collection out of these 3 Collections which will collate data (with some coalescing rules across different Source) across three collections and create one record for each PolicyId+EndDate with Source = Final. This “Collated” collection will have same _Id and same Type as original 3 collection.

This is like full outer join across 3 collection on _Id and generating new collection with (coalescing using set of rules) only one record for each unique _Id.

What are my implementations options / approach ?

I am using on-premise Mongo DB and we are interacting with Mongo DB through C# Mongo Driver.

Thanks in Advance. Mike

user2757350
  • 189
  • 2
  • 11
  • 1
    You might try aggregation with $lookup and $out/$merge – Joe Nov 29 '21 at 06:31
  • Can you elaborate ? I am trying to do coalescing across multiple collection not just one ? i.e. Field = coalesce(SourceA.Field, SourceB.Field, SourceC.Field). I am trying to do full outer join across all collection. – user2757350 Dec 01 '21 at 18:10
  • Start with https://stackoverflow.com/questions/35813854/how-to-join-multiple-collections-with-lookup-in-mongodb, then use [$out](https://docs.mongodb.com/datalake/reference/pipeline/out/index.html) to write the results to a collection. – Joe Dec 01 '21 at 18:17

0 Answers0