[SOLVED] ADF Data Flow flatten JSON to rows

ADF Data Flow flatten JSON to rows

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:

Solution

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.