what to convert json file into csv file using copy activity in adf

Question

for example json

{

"heading" : [ "id", "standard" ,"name"],

"rows" : [[1,1,"a"],[2,1,"b"],[3,2,"c"]]

}

csv should be like

id, standard, name

1,1,a

2,1,b

3,2,c

Answer 1

Hi Cloud Admin - Ankit Kumar Bansal (g) ,

Welcome to Microsoft Q&A platform and thanks for posting your question here.

As I understand your query, you are trying to transform the JSON of nested Array into tabular structure. Please let me know if that is not the ask.

Looking at the JSON it seems due to inconsistency in the datatype of array items, it's not pulling all the data when we are previewing the source data. In order to overcome that, I have done minute change of adding " in all the items of array:

{  "heading" : [ "id", "standard" ,"name"],  "rows" : [["1","1","a"],["2","1","b"],["3","2","c"]]  }

• Create a new branch out of the source , and attach two flatten transformation respectively. One having 'unroll by' as 'rows' and other as 'heading' . Since 'rows' is a nested array. We need to flatten it twice by using another flatten transformation again. Refer below

• Use surrogate key to generate IDs ID1 and ID2corresponding to each records for both the branches 'rows' and 'headings'.
• Then use derive column transformation for 'rows' branch to create a new column 'ranking' and provide the below expression to divide the whole set of records into set of 3 : iif(id2%3==0,3,toInteger(id2%3))
• Now use Join transformation to join the two datasets using the ID1 column and ranking column
• Use select transformation to deselect all other unnecessary columns except 'heading' and 'rows'.
• Use Sort transformation to sort by ascending order of 'heading' column. Use another surrogate key to create 'ranking' column again using same expression: iif(rownum%3==0,3,toInteger(rownum%3)).Use Sort transformation to sort by ranking column in ascending order .
• Use Pivot transformation and group by using 'ranking' column . Select 'heading' as pivot key and in pivoted column , provide this expression: max(rows)

In the Pivot transformation data preview tab, click on 'map drifted' so that it will redefine the current schema. Then, use select transformation to deselect all other columns and only keep 'ID' , 'Standard' and 'Name'

For more details, kindly check this video which is dealing with similar scenario: How to Copy Complex multi-array JSON into tabular format using mapping dataflow

Hope it helps. Kindly accept the answer if it's helpful. Thankyou