0

I'm currently working with MongoDB and Azure CosmosDB for a project and I need to filter and copy data from one to the other.

The source data is a MongoDB collection named "Purchase", where each record has an 'AccountId'. I have a container in CosmosDB, named 'Account', that contains all the _id(s) I want to match with the 'AccountId' from the MongoDB source.

The goal here is to filter and copy only those records from the MongoDB source whose 'AccountId' matches any of the _id(s) in the 'Account' container in CosmosDB.

I'm using Azure Synapse Analytics for this operation, but I'm unsure how to write the correct filter using dynamic content to achieve this aim.

Has anyone dealt with this kind of scenario before, and could you provide some guidance on how to construct the filter? Any sample code, if available, would be extremely helpful.

Thank you for your time.

enter image description here

Alireza Ghaffari
  • 1,004
  • 3
  • 11
  • 24

1 Answers1

0

How to filter data from a MongoDB source to match _id(s) in a CosmosDB container using Azure Synapse Analytics.

When you select Mongo db as a source and in Filter property you cannot use the values from other data sources like cosmos db.

to achieve your requirement, we supposed to use Data flow but Mongo db as a source is not supported there.

You can follow below workaround:

  • First take a look up activity with cosmos db as a source and get all the documents which _id(s) you want to compare with Mongo db AccountId enter image description here

  • Send the output of Lookup activity to for-each activity. enter image description here

  • In for each activity take copy activity and add source as Mongo db and filer condition for you like {AccountId:"@{item()._id}"} enter image description here

  • Select your appropriate sink and run the pipeline.

Pratik Lad
  • 4,343
  • 2
  • 3
  • 11