Convert very complex JSON to csv using ADF

Question

I have a very complex JSON format with multiple array's. Need the best solution to convert it to csv file using ADF. Output Expected : JSON format example:

JSON format example:

   {
   "elementCollection":[
      {
         "elementId":4643,
         "alias":"f97836a8-7c2e-4aaf-a0c1-551ce93b6ee9",
         "attributes":[
            {
               "Schedule":{
                  "id":"602",
                  "type":"Heading"
               }
            },
            {
               "Release_Name_in_BusinessLine_WS":{
                  "textValue":"xxx",
                  "formattedTextValue":"123",
                  "id":"545",
                  "type":"Text",
                  "xhtmlTextValue":"<div> 21</div>"
               }
            },
            {
               "Type":{
                  "expression":{
                     "valid":"true",
                     "pendingUpdate":"false",
                     "formula":"'Type' + 'EP'"
                  },
                  "textValue":"",
                  "formattedTextValue":"xxx",
                  "id":"544",
                  "type":"Text",
                  "xhtmlTextValue":"<div></div>"
               }
            }
         ]
      },
      {
         "elementId":6443,
         "alias":"f73a8cdd-c4e1-4b21-8253-44c237ea60c2",
         "attributes":[
            {
               "Schedule":{
                  "id":"602",
                  "type":"Heading"
               }
            },
            {
               "Release_Name_in_BusinessLine_WS":{
                  "textValue":"xxx",
                  "formattedTextValue":"123",
                  "id":"545",
                  "type":"Text",
                  "xhtmlTextValue":"<div> 21</div>"
               }
            },
            {
               "Type":{
                  "expression":{
                     "valid":"true",
                     "pendingUpdate":"false",
                     "formula":"'Type' + 'EP'"
                  },
                  "textValue":"",
                  "formattedTextValue":"xxx",
                  "id":"544",
                  "type":"Text",
                  "xhtmlTextValue":"<div></div>"
               }
            }
         ]
      }
   ]

Answer 1

Hi , Welcome to Microsoft Q&A platform and thanks for posting your query here. As per my understanding you want to convert JSON to CSV using ADF pipeline. Please let me know if my understanding is incorrect. This can be achieved using flatten transformation within mapping dataflow in azure data factory, however, it seems although the JSON you have provided is valid json but it's not having proper schema . The schema of objects within attribute array is not consistent. Dataflow always treats the first object of the array as the schema . So if you see inside attribute[] array, the first json is taken as the schema. It expects that all the items of array should have same schema. If it's different , it will consider the first object schema as the schema of whole array. You can check the projection tab in source to verify the same. Here is the full schema dataflow is able to detect out of the json.

So, other than elementId, alias, schedule_id, and schedule_type , other attributes can't be flattened. You may need to recheck with the source team to remodify the json or else try to opt for writing custom code in C#, or python using azure functions to achieve the requirement . Hope it helps. Kindly accept the answer by clicking on Accept answer button. Thankyou