ADF Data Flow flatten JSON to rows

Question

IN ADF Data Flow how can I flatten JSON into rows rather than columns?

{                   
                "header": [
                    {                           
                        "main": {                                
                            "id": 1                               
                        },
                        "sub": [
                            {
                                "type": "a",                                   
                                "id": 2                                  
                            },
                            {
                                "type": "b",                                   
                                "id": 3                                 
                            }
                              
                              ]}]}

In ADF I'm using the flatten task and get the below result:

However the result I'm trying to achieve is merging the two id columns into one column like below:

Saideep Arikontham · Answer

Since both main_id and sub_id belong in the same column, instead of using 1 flatten to flatten all the data, flatten both main and sub separately.

I have taken the following JSON as source for my dataflow.

{
   "header":[
      {
         "main":{
            "id":1
         },
         "sub":[
            {
               "type":"a",
               "id":2
            },
            {
               "type":"b",
               "id":3
            }
         ]
      },
      {
         "main":{
            "id":4
         },
         "sub":[
            {
               "type":"c",
               "id":5
            },
            {
               "type":"d",
               "id":6
            }
         ]
      }
   ]
}

I have taken 2 flatten transformations flattenMain and flattenSub instead of 1 which use the same source.
For flattenMain, I have unrolled by header and selected unroll root as header. Then created an additional column selecting source column header.main.id.

enter image description here

The data preview for flattenMain would be:

enter image description here

For flattenSub, I have unrolled by header.sub and selected unroll root as header.sub. Then created 2 additional column selecting source column header.sub.id as id column and header.sub.type as type column.

enter image description here

The data preview for flattenSub transformation would be:

enter image description here

Now I have applied union transformation on both flattenMain and flattenSub. I have applied union by using Name.

enter image description here

The final data preview for this Union transformation will give the desired result.

enter image description here

NOTE: All the highlighted rows in output images indicate the result that would be achieved when we use the JSON sample provided in the question.

ADF Data Flow flatten JSON to rows

1 Answers