Azure | ADF | How to use a String variable to lookup a Key in an Object type Parameter and retrieve its Value

user_10202398 0
Viewed 8937

I am using Azure Data Factory. I'm trying to use a String variable to lookup a Key in a JSON array and retrieve its Value. I can't seem to figure out how to do this in ADF.

Details:

I have defined a Pipeline Parameter named "obj", type "Object" and content: {"values":{"key1":"value1","key2":"value2"}}

Parameter definition

I need to use this pipeline to find a value named "key1" and return it as "value1"; "key2" and return it as "value2"... and so on. I'm planning to use my "obj" as a dictionary, to accomplish this.

Technically speaking, If i want to find the value for key2, I can use the code below, and it will be returned "value2":

@pipeline().parameters.obj.values.key2

What i can't figure out is how to do it using a variable (instead of hardcoded "key2").

To clear things out: I have a for-loop and, inside it, i have just a copy activity: for-each contents The purpose of the copy activity is to copy the file named item().name, but save it in ADLS as whatever item().name translates to, according to "obj"

This is how the for-loop could be built, using Python: python-for-loop

In ADF, I tried a lot of things (using concat, replace...), but none worked. The simpliest woult be this:

@pipeline().parameters.obj.values.item().name

but it throws the following error:

{"code":"BadRequest","message":"ErrorCode=InvalidTemplate, ErrorMessage=Unable to parse expression 'pipeline().parameters.obj.values.item().name'","target":"pipeline/name_of_the_pipeline/runid/run_id","details":null,"error":null}

So, can you please give any ideas how to define my expression? I feel this must be really obvious, but I'm not getting there..... Thanks.

azure-data-factory-2 azure json
2 Answers

Hello fellow Pythonista!

The solution in ADF is actually to reference just as you would in Python by enclosing the 'variable' in square brackets.

I created a pipeline with a parameter obj like yours

enter image description here

and, as a demo, the pipeline has a single Set Variable activity that got the value for key2 into a variable.

enter image description here enter image description here

This is documented but you need X-ray vision to spot it here.

user_136127450

Based on your comments, this is the output of a Filter activity. The Filter activity's output is an object that contains an array named value, so you need to iterate over the "output.value": enter image description here

Inside the ForEach you reference the name of the item using "item().name": enter image description here

EDIT BASED ON MORE INFORMATION:

The task is to now take the @item().name value and use it as a dynamic property name against a JSON array. This is a bit of a challenge given the limited nature of the Pipeline Expression Language (PEL). Array elements in PEL can only be referenced by their index value, so to do this kind of complex lookup you will need to loop over the array and do some string parsing. Since you are already inside a FOR loop, and nested FOR loops are not supported, you will need to execute another pipeline to handle this process AND the Copy activity. Warning: this gets ugly, but works.

Child Pipeline

Define a pipeline with two parameters, one for the values array and one for the item().name: enter image description here

When you execute the child pipeline, pass @pipeline.parameters.obj.values as "valuesArray" and @item().name as "keyValue".

  1. You will need several string parsing operations, so create some string variables in the Pipeline: enter image description here

  2. In the Child Pipeline, add a ForEach activity. Check the Sequential box and set the Items to the valuesArray parameter: enter image description here

  3. Inside the ForEach, start by cleaning up the current item and storing it as a variable to make it a little easier to consume. enter image description here

  4. Parse the object key out of the variable [this is where it starts to get a little ugly]: enter image description here

  5. Add an IF condition to test the value of the current key to the keyValue parameter: enter image description here

  6. Add an activity to the TRUE condition that parses the value into a variable [gets really ugly here]:

enter image description here

Meanwhile, back at the Pipeline

At this point, after the ForEach, you will have a variable (IterationValue) that contains the correct value from your original array: enter image description here

Now that you have this value, you can use that variable as a DataSet parameter in the Copy activity.

user_758380
Related