MAP Nested JSON using rest API in Azure Data Factory using for each Iterator through a stored proced-CodePudding

I am pulling a list of pipeline using REST API in ADF. The API gives a nested JSON and I am using a for each iterator to be pulled via a stored procedure to be pulled into a SQL table.

The sample JSON looks like this:

 {
  "value": [
    {
    "id": "ADF/pipelines/v_my_data_ww",
    "name": "v_my_data_ww",
    "type": "MS.DF/factories/pipelines",
    "properties": {
    "activities": [
      {
        "name": "Loaddata_v_my_data_ww",
        "type": "Copy",
        "dependsOn": [],
        "policy": {
          "timeout": "7.00:00:00",
          "retry": 0,
          "retryIntervalInSeconds": 30,
          "secureOutput": false,
          "secureInput": false
      }
     ]
     },
     "folder": {
      "name": "myData"
      },
      "annotations": [],
      "lastPublishTime": "2021-04-01T22:09:20Z"
     ,
     "etag": "iaui7881919"
         }
   }
  ]
}

So using the For Each Iterator @activity('Web1').output.value, I was able to pull in the main keys, like id, name , type. However I also need to pull in the name/type from within the properties/activities tag. not sure how to do it. I was trying the following.

When I run the pipeline, I get the following error:

The expression 'item().properties.activities.name' cannot be evaluated because property 'name' cannot be selected. Array elements can only be selected using an integer index.

Any help in this regards will be immensely appreciated.

CodePudding user response：

The error is because you are trying to access an array i.e., activities directly with a key. We need to access array elements with indices such as activities[0]

The following is a demonstration of how you can solve this. I got the same error when I used @item().properties.activities.name.

To demonstrate how to solve this, I have taken the given sample json as a parameter for pipeline and passing @pipeline().parameters.js.value in for each activity.

enter image description here

Now, I have used a set variable to show how I retrieved the name and type present in the activities property. The following dynamic content helps in achieving this.

@item().properties.activities[0].name

enter image description here

So, modify your prop_name and prop_type to the following dynamic content:

@item().properties.activities[0].name
@item().properties.activities[0].type

UPDATE:

If there are multiple objects inside activities property array, then you can follow the procedure below:

Create an execute pipeline activity to execute a pipeline called loop_activities. Create 4 parameters in loop_activities pipeline name, id, tp, activities. Pass the dynamic content as shown below:

In loop_activities pipeline, use for each to iterate through activities array. The dynamic content value for items field is @pipeline().parameters.activities.
Inside for each, you can access each required element with following dynamic content.

#for name:
@pipeline().parameters.name

#for id:
@pipeline().parameters.id

#for type:
@pipeline().parameters.type

#for prop_name:
@item().name

#for prop_type:
@item().type

The following is debug output of loop_activities (only for prop_name and prop_type) when I run pipeline1.