Need to load nested array json data to azure sql using adf

Question

Please find the sample json data sample-json.txt

It has hierarchy of arrays when I am configuring this in flatten transformation unroll root is not coming due to which it is loading data as cartesian product. So total 36 records are being loaded.

Please help.

Answer 1

Hi @grv23

The Cartesian product issue in Azure Data Factory (ADF) because you're trying to flatten two nested arrays (TankInfo[] and TankUpdates[]) in parallel without first unrolling them in separate stages. ADF Flatten can only unroll one array at a time, and trying to flatten nested arrays without respecting the hierarchy causes combinatorial explosion (6 tanks × 6 updates = 36 rows). 

To resolve this, you need to handle each array (TankInfo[] and TankUpdates[]) in separate branches of your data flow. Start by creating a source pointing to the JSON file and flatten only the TankInfo[] array. This gives you one row per tank with all the nested fields from ProductInfo and AlarmTriggers. In a separate branch, use another source for the same file and flatten the TankUpdates[] array to extract dynamic fields like temperature, stock data, and timestamps. 

Next, use a Join transformation to combine both streams based on the TankId field. This is essential to bring together static metadata (from TankInfo) and real-time data (from TankUpdates) in a single row. The join type should be Inner or Left, depending on whether you want to filter unmatched tanks. After the join, you can use a Select transformation to flatten deeper nested fields like ProductInfo.Name, Temperature.Current, or ProductStockData.Level, and rename columns for clarity. 

Finally, connect this to a Sink pointing to your Azure SQL Database. Define a schema that matches the flattened structure and configure mapping if needed. This structured approach ensures that your data lands in SQL accurately, without duplication or mismatch. It’s also flexible enough to handle additional nested structures in the future. Let me know if you'd like a visual diagram or exportable ADF JSON template for this setup. 
Please refer this Microsoft Doc for more information
https://learn.microsoft.com/en-us/azure/data-factory/data-flow-flatten

I hope this information helps. Please do let us know if you have any further queries.

Kindly consider upvoting the comment if the information provided is helpful. This can assist other community members in resolving similar issues.

Thank you.