Flattening nested Json document in data factory data flows

Edwin 1 Reputation point

Need help to flatten a complex josn document using data flows. 219488-image.png

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.

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,070 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. BhargavaGunnam-MSFT 23,771 Reputation points Microsoft Employee

    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.


    1 person found this answer helpful.
    0 comments No comments

  2. Edwin 1 Reputation point

    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.