CODEWITHSUNDEEP
azureazure-data-factory
Dinesh Madhup
Dinesh Madhup

Reputation: 367

How to flatten JSON data in Azure Data Factory?

I have Source from SQL DB where data stored as JSON. Please see below image where record column has contains JSON.

enter image description here

The record column contains JSON Data like below. I am trying to flatten the JSON column so that each item inside the JSON suppose to be in tabular form. Is it possible to do this in Azure Data Factory?

{
   "resourceType":"Encounter",
   "id":"9i5W6tp-JTd-24252",
   "meta":{
      "versionId":"1591978948308",
      "lastUpdated":"2020-06-12T16:22:28.308Z"
   },
   "status":"finished",
   "class":{
      "system":"http://terminology.hl7.org/CodeSystem/v3-ActCode",
      "code":"AMB"
   },
   "type":[
      {
         "coding":[
            {
               "system":"http://snomed.info/sct",
               "code":"185345009",
               "display":"Encounter for symptom"
            }
         ],
         "text":"Encounter for symptom"
      }
   ],
   "subject":{
      "reference":"Patient/9i5W6tp-JTd-24249",
      "display":"Mrs. tonia30 back373"
   },
   "participant":[
      {
         "individual":{
            "reference":"Practitioner/9i5W6tp-JTd-24251",
            "display":"Dr. Stevie682"
         }
      }
   ],
   "period":{
      "start":"1962-05-27T08:26:51Z",
      "end":"1962-06-10T08:26:51Z"
   },
   "reasonCode":[
      {
         "coding":[
            {
               "system":"http://snomed.info/sct",
               "code":"232353008",
               "display":"Perennial allergic rhinitis with seasonal variation"
            }
         ]
      }
   ],
   "serviceProvider":{
      "reference":"Organization/9i5W6tp-JTd-24250",
      "display":"HOSPITAL"
   }
}

Upvotes: 0

Views: 3510

Answers (2)

Leroy Mason
Leroy Mason

Reputation: 11

I'm currently using ADF data flows can flatten JSON.

Upvotes: 1

Leon Yue
Leon Yue

Reputation: 16401

I'm agree with @Mark Kromer. We can not flatten a JSON doc embedded inside a column in ADF data flows today. It's impossible for now.

Suggestions:

  1. Copy the SQL table data to the sink as the JSON format file.
  2. Then use the exported JSON format file as source and flatten the JSON array to get the tabular form.

That's the workaround for the issue. We hope Data Factory product team can make progress and update us soon. Waiting for the good news and thanks Mark again.

Upvotes: 2

Related Questions