Flatten a nested JSON array in ADF copy activity

Question

How can I flatten an array of JSONs that contain another array inside it, in the copy activity. I review the forumns and samples. I've already tested many approaches, but it seems my JSON is very complecated. This is the JSON:

{
    "results": [{
            "label": "CA - SF",
            "disOrd": 6,
            "id": "1234",
            "stages": [{
                    "label": "R D",
                    "disOrd": 0,
                    "metadata": {
                        "isClosed": "true",
                        "probability": "0.0"
                    },
                    "id": "567",
                    "createdAt": "2023-05-22T13:31:50.317Z",
                    "updatedAt": "2023-05-30T16:07:47.170Z",
                    "arch": false,
                    "writePer": "CRM"
                }, {
                    "label": "P D",
                    "disOrd": 1,
                    "metadata": {
                        "isClosed": "true",
                        "probability": "0.0"
                    },
                    "id": "890",
                    "createdAt": "2022-10-21T10:04:15.298Z",
                    "updatedAt": "2023-05-30T16:07:47.170Z",
                    "arch": false,
                    "writePer": "CRM"
                }
            ],
            "createdAt": "2021-07-19T16:06:35.682Z",
            "updatedAt": "2023-05-30T16:07:47.170Z",
            "arch": false
        }, {
            "label": "IO",
            "disOrd": 14,
            "id": "2345",
            "stages": [{
                    "label": "P-D",
                    "disOrd": 0,
                    "metadata": {
                        "isClosed": "true",
                        "probability": "0.0"
                    },
                    "id": "987",
                    "createdAt": "2023-05-30T16:06:22.624Z",
                    "updatedAt": "2023-05-30T16:06:22.624Z",
                    "arch": false,
                    "writePer": "CRM"
                }, {
                    "label": "I C",
                    "disOrd": 1,
                    "metadata": {
                        "isClosed": "false",
                        "probability": "0.05"
                    },
                    "id": "876",
                    "createdAt": "2020-04-15T16:17:02.063Z",
                    "updatedAt": "2023-05-30T16:06:22.624Z",
                    "arch": false,
                    "writePer": "CRM"
                }, {
                    "label": "GI",
                    "disOrd": 2,
                    "metadata": {
                        "isClosed": "false",
                        "probability": "0.25"
                    },
                    "id": "543",
                    "createdAt": "2020-04-15T16:17:02.063Z",
                    "updatedAt": "2023-05-30T16:06:22.624Z",
                    "arch": false,
                    "writePer": "CRM"
                }               
            ],
            "createdAt": "2020-04-15T16:17:02.063Z",
            "updatedAt": "2023-05-30T16:06:22.624Z",
            "arch": false
        }       
    ]
}

I want to copy the flatten JSON into a csv file. I used a varaible that has the dynamic mapping in the copy activity, then its index has been changed in a loop but the problem occured with double quotes. Is there any solution?

Pratik Lad · Accepted Answer · 2023-10-10 08:05:54Z

2

You want to copy hierarchical Json data which contain nested arrays using Copy activity in ADF. This is possible up to one level of hierarchy by using collection reference option in mapping tab of Copy data activity.

As per this this MS Document

enter image description here

To achieve your requirement, you need to use the data flow and the flatten trans formation.

First create a data flow and add the source as your Json file.
Take a flatten transformation and Under Unroll by select your arrays to be flatten and create appropriate columns using add mapping.

Output:

enter image description here

UPDATE:

Using copy activity:

As copy activity has limitation of flattening single array at a time you need to use two copy activities.

First flatten the result array. Take one copy activity and flatten result array as follow and store in the Json file.

Source setting (take initial file in source)
Sink setting (store the flattened result array to another Json file)
Mapping should look like below:
Output stored in second json file is:

Now flatten the stage array from our newly stored Json file. Take another copy activity and flatten stages array as follow and store in the csv file.

Source setting (Json file stored in first copy activity)
Sink setting (store the flattened stages array to CSV file)
Mapping should look like below:
Output stored in CSV file is:

edited Oct 10, 2023 at 8:05

answered Oct 10, 2023 at 4:36

Pratik Lad

8,7282 gold badges5 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mehrsa Over a year ago

Thanks for the response, but based on our company's policies we shouldn't use data flow.

Pratik Lad Over a year ago

In Copy activity You can only flatten single array at a time.

Collectives™ on Stack Overflow

Flatten a nested JSON array in ADF copy activity

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related