Combine values from a json in an Azure dataflow into one large array

I have a json object per row in an Azure dataflow and want to append all values to an array and then flatten it, so that each element of the array is a value rather than all values for that specific row.

My input data looks like this:

and I want my final output to look like:

so that I can use the data in a ForEach pipeline activity and loop through each id.

I have the below solution that provides my expected output, where each select activity following the flatten selects a specific column (one of the key-value pairing). This is not a good solution because as my keys expand so too will the select activities required. I would like this to be dynamic, based on the keys in the json.

You can try the below approach, but this will only work in this case where there are not nested structures in your JSON strings and the values should not contain the special character Double quote (").

First take a derived column transformation after your source. Here, Create a new column sub_arr with below expression.

slice(map(split(Column,'": "'),split(#item,'"')[1]),2)

This will first split the JSON string on '": "' and then for each string, it will again split the sub record string on '"' and takes first item. It means, it will create the array of values for each JSON string row as shown below.

Next, to combine these arrays of each row, take an Aggregate transformation and create the required res_arr column in the aggregate section with below expression. Here, no need to take any column in Group By section.

flatten(collect(sub_arr))

Now, it will give the expected array as shown below.