CODEWITHSUNDEEP
azureazure-data-factory
coder_andy
coder_andy

Reputation: 416

ADF expression to convert array to comma separated string

This appears to be pretty basic but I am unable to find a suitable pipeline expression function to achieve this.

I have set an array variable VAR1 with the following value, which is an output from a SQL Lookup activity in an ADF pipeline:

[
    {
        "Code1": "1312312"
    },
    {
        "Code1": "3524355"
    }
]

Now, I need to convert this into a comma separated string so I can pass it to a SQL query in the next activity - something like:

"'1312312','3524355'"

I am unable to find an expression function to iterate over the array elements, nor convert an array to a string. The only pipeline expression functions I see are to convert string to array and not the other way around.

Am I missing something basic? How can this be achieved?

Upvotes: 5

Views: 27457

Answers (5)

Fabien Arnaud
Fabien Arnaud

Reputation: 1

For those who wonder how the ForEach loop needs to be set up, you need a temp variable and a final variable.

full

Make sure that the ForEach is set to sequential or the content of your variables will be totally random!

foreach

This is how I set up my first (final) variable. In my case I used a semi colon as a separator since my items are email addresses.

final variable

The second variable is the temp variable that will temporarily keep the value until the next iteration, as because ADF just doesn't allow a variable to reference itself :(.

temp variable

Upvotes: 0

Dylan Schultz
Dylan Schultz

Reputation: 61

I had this same issue and was not totally satisfied just using the join function because it keeps the keys from the json object. Also, using an iterator approach can work but is needlessly expensive and slow if you have a long list. Here was my approach, using join and replace:

replace(replace(join(variables('VAR1'), ','), '{"Code1":', ''), '}', ''))

This will give you exactly the output you are looking for.

Upvotes: 6

Seemant Singh
Seemant Singh

Reputation: 180

Use 'join' function present in 'collection' functions in 'Add dynamic content'. For example:

join(variables('ARRAY_VARIABLE'), ',')

Upvotes: 12

coder_andy
coder_andy

Reputation: 416

I got it working using a ForEach loop activity to iterate over my array and use a Set Variable task with a concat expression function to create my comma separated string.

Wish they had an iterator function in the expression language itself, that would have made it much easier.

Upvotes: 1

Rajat Arora
Rajat Arora

Reputation: 606

In case, you just have two elements in the array, then you can do something like:

@concat(variables('variable_name')[0].Code1, ',', variables('variable_name')[1].Code1)

Upvotes: -3

Related Questions