MAP Nested JSON using rest API in Azure Data Factory using for each Iterator through a stored procedure to a SQL table

Question

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.

Saideep Arikontham · Accepted Answer · 2022-09-07 07:01:23Z

1

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.

edited Sep 7, 2022 at 7:01

answered Sep 7, 2022 at 5:07

Saideep Arikontham

6,1922 gold badges6 silver badges14 bronze badges

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

5 Comments

Stan Over a year ago

another quick question for you. If have multiple name/ype tags inside parameters/activities tags , will this help, I am seeing only the first such item being picked up. Is there a way we can use another for loop within each parameters/activity tags?

Saideep Arikontham Over a year ago

If there are multiple objects say activities[0], activities[1],..., you can use another for each to loop through this array. But, in azure data factory, you can't use nested for each directly. So, you will have to create an execute pipeline (which contains for each to loop through activities) inside your above for each activity. To this execute pipeline, pass the activities array along with others to respective parameters. Then inside the other pipeline, you can use passed activities array to iterate through each object and pass them to stored procedure as @item().name and @item().type

Stan Over a year ago

Thanks @Saideep. can you please provide me an image in Azure DF of how to do that using the execute pipeline you mention. Or maybe a diagram . That will help. Thanks in anticipation. Actually I understand what you are saying, but not sure how to do that. If you can sketch something similar in Azure and provide an image, I will try that

Saideep Arikontham Over a year ago

I have added an Update section to my answer. Please refer to it.

Stan Over a year ago

Thank you @Saideep. let me go through and implement. I will keep you posted. Immense Thanks.

Collectives™ on Stack Overflow

MAP Nested JSON using rest API in Azure Data Factory using for each Iterator through a stored procedure to a SQL table

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related