CONVERT JSON TO CSV DATA FACTORY

Question

Hi, i have a json file have json string with a part of array like the following
{
"@odata.etag": "W/\"JzEtVGFzayAgQEBAQEBAQEBAQEBAQEBASCc=\"",
"planId": "svNEhpdwSke0X2-FgAVG5MgAFwA9",
"bucketId": "kvBViBc6B0icQBEI_rgAS8gABHYC",
"title": "test",
"orderHint": "8585573973156050658PJ",
"assigneePriority": "",
"percentComplete": 0,
"createdDateTime": "2022-02-07T04:53:50.0756675Z",
"hasDescription": false,
"previewType": "automatic",
"referenceCount": 0,
"checklistItemCount": 0,
"activeChecklistItemCount": 0,
"id": "fYo97Y549UqD9YkjUKE5ucgAH9Zv",
"createdBy": {
"user": {
"id": "47780eca-af3b-4c0f-ae07-cf88a920e32c"
}
},
"appliedCategories": {},
"assignments": {},
"_assignments": []
},
{
"@odata.etag": "W/\"JzEtVGFzayAgQEBAQEBAQEBAQEBAQEBAaCc=\"",
"planId": "svNEhpdwSke0X2-FgAVG5MgAFwA9",
"bucketId": "Dihv6ICpT02JcOKoSrlOFMgANcgI",
"title": "Azure day 6 Databricks",
"orderHint": "8585537543107831691Pa",
"assigneePriority": "8585537542507518356",
"percentComplete": 100,
"createdDateTime": "2022-03-21T08:50:34.7257451Z",
"dueDateTime": "2022-03-22T10:00:00Z",
"hasDescription": false,
"previewType": "automatic",
"completedDateTime": "2022-03-22T13:23:01.2534571Z",
"referenceCount": 0,
"checklistItemCount": 0,
"activeChecklistItemCount": 0,
"id": "ZSbpiYsyXEK50Kvtom88WcgALUkC",
"createdBy": {
"user": {
"id": "5bfd5db0-8b4e-4809-a54a-2564c68d34d4"
}
},
When i use Datafactory copy data to csv, the mapping cannot recognize the completedDateTime
How can i do this. I want this recognize when completedDateTime exist or return Null if not

Answer 1

Hi @Nguyen Van Hung (FA.G0.HN) ,

Thank you for posting query in Microsoft Q&A Platform.

When we create datasets on top of json file which has some arrays. Then, schema will be considered based on first item in array.

In your case completedDateTime filed is missing in first item and its present in second item. Hence ADF dataset is not able to see completedDateTime field.

You can consider doing any one of following.

• Have a sample json file, with proper schema in it. That means first item in array also should has completedDateTime field in it. And take schema of dataset from the sample json file.
  

OR

• Either manually edit source json file to include completedDateTime field Or Check with source if they can send source file array items properly with all fields.

OR

• Use Azure Functions or some custom code to modify source json file to include completedDateTime filed in first item of array.

Hope this helps. Please let us know how it goes. Thank you.