Flattening nested Json document in data factory data flows

Question

Need help to flatten a complex josn document using data flows.

How Can I flatten the column values under Parlors(which would be dynamic), which has values from 1 to 6. Can I use something like Parlors{1}, Parlors{2}... to get values under each parlors? Please refer the attached image.

Answer 1

Hello @Edwin ,
Welcome to the Microsoft Q&A platform, and thanks for posting your query.
Please correct me if my understanding is wrong. You are looking to unroll the array values inside the JSON into individual rows.

Based on your screenshot I am assuming below is your JSON.

{
"Parlors": ["1", "2", "3", "4", "5", "6"]
}

Here are the steps on how to use the flatten transformation to unroll the array values into individual rows.

My source file is pointed to the JSON file on the blob storage.

On the projections tab, click on import projects and you can see the column “Parlors”
Add flatten transformation and in the flatten settings you can see “Unroll by”. Select the Parlors and map the input column.

On the inspect tab, you can see input and output columns

Go to data preview and click on refresh to see the values.

here are my output values with the flatten transformation.

Please check the video tutorial below to understand more about Flatten transformation.

https://www.youtube.com/watch?v=zrjYg2_2Y9I&t=619s&ab_channel=WafaStudies

Answer 2

Hi @BhargavaGunnam-MSFT , thanks for your response. My source dataset has values inside each parlors as array/complex type. For Ex , parlors.{1}.tech1.{1}.identifier, value = parlors.{1}.tech1.{1}.value)., parlors.{2}.tech1.{2}.identifier, value = parlors.{2}.tech1.{2}.value). I have to get the Identifier and Value for each Parlor and that should be dynamic, as the number of Parlors may change for each file. Not sure if the no of parlors can be passed as parameter and the flatten hierarchy to be executed for each value.