Scenario - I recently encounter a problem, where I was supposed to build a data integration pipeline from API to Sync data to Azure SQL Database.
The JSON structure was dynamic as highlighted in the below screenshot and it can change for each record.
Solution :- To handle this scenario with in Azure Data Factory we need to use Data Flow.
In below screenshot we can see the projection is not right because complex structure is represented as string.
** The Project needs to be right because it will be used by further activities.
Now, as we have got the right project in place now let's look at how to break this complex type into row/tabular format.
Let's preview the data, we can see that we have array which contains complex and non-complex data types.
We need to make use of flatten activity to convert array to row format and also to flatten the complex type to get the values inside the complex type.
Select unroll by results[] and then click on reset
We need to make use of flatten activity to convert array to row format and also to flatten the complex type to get the values inside the complex type.
Select unroll by results[] and then click on reset
This is how the input columns looks like. As you can see the custom_values column is not in flatten state. To flatten it we need to use Rule Based
Delete the custom_values column mapping and click on rule based column mapping.
Click on the double down icon.
Let's preview the data now. As you can see the data was earlier in array format and inside array we have another complex type but after the flatten activity the result produced is in tabular format.
We can now use the flatten data in subsequent activities to do further operations.
In the Enter Matching Condition type true() and in Enter output column name expression type $$.
Enable Deep Column Traversal and under Hierarchy Level Select custom_field values and the column will look like below.
Let's preview the data now. As you can see the data was earlier in array format and inside array we have another complex type but after the flatten activity the result produced is in tabular format.