Data factory copy activity parse JSON array only first row.

Question

Hi,

I'm using data factory copy activity to parse a JSON array like this:

{
  "nodes": [
    {
      "name": "aks-lin02-31061480-vmss000000",
      "cpu": {
        "requests": "1840m",
        "requestsPercent": "96%",
        "limits": "10640m",
        "limitsPercent": "560%",
        "utilization": "183m",
        "utilizationPercent": "9%"
      },
      "memory": {
        "requests": "4092Mi",
        "requestsPercent": "32%",
        "limits": "13486Mi",
        "limitsPercent": "107%",
        "utilization": "10528Mi",
        "utilizationPercent": "83%"
      },

In the mapping panel of copy activity, I setup the Collection reference: $['nodes']; Source: $['pods'][0]['cpu']['requests']

It returned only the first row. I think it should cause by the index number [0], how can I update it to fetch all rows? Or can someone provide related tech documentation? Thanks.

kind regards,

BoX

Answer 1

@Bo Xiao

Thanks for the question and using MS Q&A platform.

You’re correct that the index number [0] is causing the copy activity to return only the first row. To fetch all rows, you need to remove the index and use a dynamic expression to traverse through all the elements in the array.

Here are some steps you can follow:

• Use a Lookup Activity: You can use a Lookup activity with a JSON linked service to fetch the JSON data.

• Use a ForEach Activity: After fetching the JSON data, you can use a ForEach activity to loop through each element in the JSON array.
• Use Dynamic Expressions: Inside the ForEach activity, you can use dynamic expressions to parse the JSON data1. For example, you can use @array(activity('Lookup1').output.value) to parse the array of JSON.
• Use a Copy Activity: Inside the ForEach loop, use a Copy activity to copy the parsed data to your desired destination.

Alternatively, you can use a Data Flow activity with a Flatten transformation to flatten the JSON array

Hope this helps. Do let us know if you any further queries.