How to Flatten Multiple Arrays from a Single SQL Server Record in ADF Mapping Data Flow?

Question

Hello,

I’m working with Azure Data Factory’s Mapping Data Flow to process data from a SQL Server table. In my table, each record contains several columns where values are stored as string representations of arrays. For example, I have columns like:

utcCreated (e.g., "[1737489814047,1733434384005,...]")

utcUpdated (e.g., "[1737489833880,1733434434018,...]")

batchId (e.g., "[01JJ57D4FJXMGNA2M6G3NGZRG7,01JECBV7GCNKEMWXC25VW5VCVZ,...]")

batchStatus (e.g., "[success, success,...]")

Example input:

My goal is to “flatten” these arrays so that each corresponding element across the arrays becomes its own row (e.g., the first elements of all arrays become one record, the second elements become another, etc.).

1. Example output:
2. The Issues I’m Facing:

• I can flatten just a single array successfully, but when it comes to do so with parallel arrays, it does not work.

I attempted to simulate a zip operation using functions like dropLeft(), dropRight(), split(), and even tried using mapIndex() with byPosition(), but I keep running into syntax errors and type mismatch issues.

My Questions:

1. Best Practice: What is the recommended approach to flatten multiple array columns from a single SQL Server record in ADF Mapping Data Flow? I appreciate any insights, workarounds, or examples you can provide to help resolve these challenges. Thank you in advance!

Answer 1

@Max Teitelbaum

Flattening multiple arrays from a single SQL Server record in Azure Data Factory (ADF) Mapping Data Flow can be challenging, but it's definitely achievable.

Steps to Flatten Multiple Arrays

Use the Flatten Transformation:

In ADF Mapping Data Flow, use the Flatten transformation to unroll array values inside hierarchical structures like JSON into individual rows. This process is known as denormalization

You can unroll multiple arrays in a single Flatten transformation by clicking the plus (+) button next to the "Unroll by" property to add more arrays.

Configure the Flatten Transformation:

Unroll by: Select the arrays you want to unroll. The output data will have one row per item in each array.

Unroll root: Optionally, select an array as your unroll root. This must be an array of complex objects that either is or contains the unroll by array.

Use Meta Functions:

Utilize ADF's meta functions like name and type to find arrays to unroll in your data using patterns.

MapIndex Function:

For correlating values between multiple arrays, use the mapIndex function. This function helps merge arrays together and then flatten them into a relational form

Example: mapIndex(utcCreated, @(utcCreated = #item, utcUpdated = utcUpdated[#index], batchId = batchId[#index], batchStatus = batchStatus[#index])).