Azure Data Factory - forEach - JSON to array errors

Question

My aim is to use Azure Data Factory to copy data from one place to another using REST API. The first part of the copying is using the ForEach activity to select parameters from a nested JSON/array. I have had problems with this nested JSON because of various error messages in the ForEach activity.

My JSON is of the following form:

(
    {
        "key_1": "value_1",
        "key_2": [
            "value_2_1",
            "value_2_2"
        ]
    }
)

and first I'm setting the @json conversion function in front of it:

@json(
     '{
        "key_1": "value_1",
        "key_2": [
        "value_2_1",
        "value_2_2"
      ]
     }'
)

Here you can see the Execute Pipeline object and its parameters:

I am setting this JSON as a parameter in the Execute Pipeline object. I am setting its type as an "Array". (For the record, I have still had similar error messages even though I have tried to change the parameter type to "String" or "Object".)

The ForEach activity is used to select an item from the nested JSON, which is written into a parameter.

This immediately produces the following error in the Execute Pipeline activity:

Operation on target... ...failed: The function 'length' expects its parameter to be an array or a string. The provided value is of type 'Object'.

So, even though I set the JSON parameter type to "Array", it is changed into "Object" when I debug the pipeline activity. See the error below:

Next, I tried to use the ADF @createArray function before my JSON text.

@createArray(
    '{
        "key_1": "value_1",
        "key_2": [
        "value_2_1",
        "value_2_2"
      ]
     }'
)

When debugging, the forEach activity throws me an error in the first Copy data activity:

The expression 'concat(item().SELECTING_key_1_FROM_MY_JSON))' cannot be evaluated because property 'key_1' cannot be selected. Property selection is not supported on values of type 'String'.

Please help me, what I am doing wrong in my attempts of converting the JSON to an array? What should I change in my code?

So far I have tried changing the parameter type and using various functions in the JSON dynamic content but with no luck.

Joseph Xu · Accepted Answer · 2021-02-03 01:15:07Z

1

update:
If you want change the filename in Copy Data --> Sink. You can key in the dynamic content @concat(pipeline().parameters.Pip_Object.key_1,'.json') to rename the file.

Please correct me if I understand you wrong.

First, we should use Parameters to store the Json array as follows. Because Variables are not support to store the Json array. Variables are only support to store simple data type such as ["1","2","3"...].
Json array format should as follows:
[{"key_1": "value_1"},{"key_2": ["value_2_1","value_2_2"]}]
I created a simple test here. Then ForEach the Json array:
Inside ForEach1 activity, pass @item() to the object type parameter Pip_Object.
The Input is as follows:

edited Feb 3, 2021 at 1:15

answered Feb 2, 2021 at 3:19

Joseph Xu

6,1432 gold badges7 silver badges17 bronze badges

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

8 Comments

jeppoo1 Over a year ago

Xu thank you, your solution seems to be working to this point! I have created a Copy Data block inside the Execute Pipeline, and I would then like to refer to key_1 and key_2 inside the Copy Data Source: @concat(Pip_Object.key_1,'?',join(Pip_Object.key_2,'&')) but this code is not working. Can you help me with referring to the JSON keys inside Copy Data?

Joseph Xu Over a year ago

I'm a little confused about that, do you want to convert this json array into a query string for the Rest API?

Joseph Xu Over a year ago

Maybe you can post another question to clarify the process of your Copy activity. I will try to find out.

Joseph Xu Over a year ago

Hi @jeppoo1, please see my updated answer.

jeppoo1 Over a year ago

Hi @Joseph Xu that was exactly what I was looking for! Thank you!

|

Jeremy Caney · Accepted Answer · 2022-05-18 00:20:08Z

1

enter image description here

Add dynamic content above using any combination of expressions, functions and system variables at for each settings @activity('Get Metadata1').output.childItems

edited May 18, 2022 at 0:20

Jeremy Caney

7,818115 gold badges58 silver badges86 bronze badges

answered May 17, 2022 at 8:36

ragurla8

111 bronze badge

Comments

jeppoo1 · Accepted Answer · 2021-02-17 08:08:04Z

We ended up using two pipelines: a generic one and a specific one.

The generic one has the JSON as a parameter (JSON_PARAMETER), and we set it a default value in the following form

[{"key_1":"value_1","key_2":["value_2"]},{"key_1":"value_3","key_2":["value_2"]}, ...etc. ...}]

The generic pipeline has a forEach loop, in which that mentioned JSON parameter is called in Settings -> Items:

@JSON(pipeline().parameters.JSON_PARAMETER)

In the specific pipeline, there is an Execute Pipeline activity, where the JSON_PARAMETER is found in Settings -> Parameters. If the default value of JSON_PARAMETER is used, the field is left blank. If we want to change the parameter, before Execute Pipeline, we put a Set Variable activity where we change the Variables -> Value to:

@concat('
[
    {"key_1":"value_1",
     "key_2":
            ["value_2",
             "value_3"
            ]
     },
     {"key_1":"value_3",

    ...and so on...

    }
]
')

Collectives™ on Stack Overflow

Azure Data Factory - forEach - JSON to array errors

3 Answers 3

8 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

8 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related