Flattening nested Json document in data factory data flows

Edwin 1 Reputation point
2022-07-11T14:37:47.217+00:00

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
    2022-07-11T22:47:26.127+00:00

    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.

    219682-image.png

    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.

    219683-image.png

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

    219693-image.png

    Go to data preview and click on refresh to see the values.
    219644-image.png

    here are my output values with the flatten transformation.
    219616-image.png
    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

    1 person found this answer helpful.
    0 comments No comments

  2. Edwin 1 Reputation point
    2022-07-12T07:50:23.18+00:00

    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.