Map nested JSON (Mongo ATLAS) to SQL [Azure Data Factory]

Question

I want to map nested json to sql table (Microsoft SSMS)

Source is a Dataset of MongoAtlas & Sink is a Dataset of Azure SQL Database Managed Instance

I am able to map parentArray using collection reference. but not able to select child under it. also childArrays are kind of scalar arrays (they don't have any keys)

Note : I tried the option Map complex values to string but it is putting the values in column cell like ["ABC", PQR] which I dont want is there any way to map it ?

Expected output for Table : childarray2

Answer 1

Currently in ADF, Copy Activity supports mapping of arrays for only 1 level. There is not way to map nested arrays.

For this I had to use Data flows. Limitation was, we cannot use mongoDB/mongo Atlas as a input source in Data flow, so the workaround was

1. Convert Mongo To Azure Blob JSON (Copy Activity Task)
2. Use Azure Blob JSON files as a input source and then SQL tables as sink

Note: You can select this option to delete you blob files, to save storage space