Flatten and Parsing Json using Azure Data Flow

Krishna Nagesh Kukkadapu 116 Reputation points

I have table which has the following data

Code Info
AE [{"key":"eng","value":"ABC"},{"key":"fra","value":"DEF"}]
US [{"key":"eng","value":"XYZ"},{"language":"dut","value":"123"}]
UK [{"key":"arb","value":"KLM"}]
I want to transform it using Azure Data Flow as below

Code InfoKey InfoValue
AE eng ABC
AE fra DEF
US eng XYZ
US dut 123
UK arb KLM
I had tried using flatten transformation, parsing transformation and nothing was succeeded. when I use flatten transformation on Column 'Info', it gives the same output. and when i tried parsing json, its not able to transform the data at all.

Could someone help how we can transform this data.

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

Accepted answer
  1. ShaikMaheer-MSFT 31,806 Reputation points Microsoft Employee

    Hi @Krishna Nagesh Kukkadapu ,

    Welcome to Microsoft Q&A Platform. Thank you for posting query here.

    You need to use Derived Column transformation to convert your json objects as array items and then use Flatten Transformation to flatten that array and then use Parse transformation to make json as columns.

    I implemented your scenario. Please check below step by step explanation.

    Step1: Source Transformation, which takes your data into dataflows.


    Step2: Derived Column Transformation, to Convert your "Info" Column as array of json objects.
    expression used: split(replace(replace(replace(Info,'[',''),']',''),'},{','}|{'),'|')


    Step3: Flatten Transformation, to flatten "Info" column array values.


    Step4: Parse Transformation, to parse json data as columns.
    output column type expression used: (key as string, value as string)


    Step5: Inside Sink Transformation, disable auto mapping and do mappings as per requirement.


    Hope this will help. Please let us know if any further queries.


    • Please don't forget to click on 130616-image.png or upvote 130671-image.png button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
    • Want a reminder to come back and check responses? Here is how to subscribe to a notification
    • If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators
    2 people found this answer helpful.

0 additional answers

Sort by: Most helpful