Flatten and Parsing Json using Azure Data Flow

Question

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.

Answer 1

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 or upvote 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