Parse Array API Response Data Factory Copy Activity

Question

Hi,

I have a API response (show at the bottom) I need to load into blob storage and then into a data warehouse table to do some reporting on. The response seems to have 2 arrays "tags" and "riskCauses" which I need to extract out all the data from and get into a a warehouse table. The copy activity in ADF will only pull the first piece of data in each array, is there a way to dump the whole array into a column so I can parse the data later on? It looks like the size of the data is dynamic, so it'll change dependent on the record and I'll have to figure out how many static columns I require to hold all the tag and riskCauses id's into the data warehouse tables.

Kind Regards

Glyn

[
  {
    "totalCount": 1,
    "records": [
      {
        "id": 100106,
        "name": "Test",
        "status": 0,
        "description": "Test",
        "owner": {
          "id": 102758,
          "name": "Jules Gale"
        },
        "tags": [
          {
            "id": 10175,
            "name": "Test Tag 1"
          },
          {
            "id": 10160,
            "name": "Test Tag 2"
          },
          {
            "id": 10163,
            "name": "Test Tag 3"
          }
        ],
        "completed": true,
        "createDate": "2022-02-23 11:54:46.929",
        "createdBy": {
          "id": 2760,
          "name": "Test.Consulting"
        },
        "lastModifiedBy": {
          "id": 100760,
          "name": "Test User 1"
        },
        "lastModifiedDate": "2022-12-14 11:05:07.371",
        "riskCauses": [
          {
            "id": 100009,
            "name": "Test Cause 1",
            "status": 0
          },
          {
            "id": 100013,
            "name": "Test Cause 2",
            "status": 0
          },
          {
            "id": 100066,
            "name": "Test Cause 3",
            "status": 0
          },
          {
            "id": 100008,
            "name": "Test Cause 4",
            "status": 0
          }
        ]
      }
    ],
    "maxPage": 50
  }
]

Answer 1

@Glyn Thomas Thanks for using Microsoft Q&A forum and posting your query.

As per my understanding your API response has nested array fields. In that case copy activity is not ideal for such scenarios. Copy activity is only intended for data movement and not best for data transformation.

Since you would like to transform the data format, Mapping data flow is best suited for this use case scenario.

Two things you can do here:

Solution 1: Either you can load the API JSON response to a blob storage as is using a copy activity and then use a mapping data flow and point to that blob as source and then use flatten transformation in mapping data flow to flatten the nested arrays.

OR

Solution 2: You can directly use a Mapping data flow instead of Copy activity and in the source use REST connector and then followed by Flatten transformation to flatten the nested arrays of API response and then you can have two streams one to save it to storage and another to load the data to your Datawarehouse. You can choose which ever path as per your need.

For flatten transformation you can refer to this demo video which has detailed explanation on how to flatten the arrays: Flatten Transformation in Mapping Data Flow in Azure Data Factory

Hope this helps.

---

Please don’t forget to Accept Answer and Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members.