1

I retrieve activity('GetData').output.value and the result is :

'[{\"name\":\"8.0\"},{\"name\":\"8.1\"},{\"name\":\"7.2\"}]'

but I need

"8.0","8.1","7.2"

Do you have any ideas how to convert? I've tried with ForEach activity and then use item().name but there is an error without message.

inspiredd
  • 195
  • 2
  • 11
  • Do you need to iterate through it or do you need the combined string? Either way, use a For Each loop. Specify the items and then use @item().name within the loop. Use the Append Variable activity inside the loop if you need to assemble the concatenated string. – wBob Jul 10 '21 at 10:44
  • Inside a loop I have a copy activity which retrieve data from API (item().name is a part of query). Why append variable? – inspiredd Jul 10 '21 at 12:03
  • Only use Append if you need to construct your string like: "8.0","8.1","7.2". If you are just iterating then you do not need to do that, it wasn’t clear from your question. – wBob Jul 10 '21 at 12:10
  • I've used append variable but I receive : '[\"8.1\",\"8.0\",\"7.2\"]. – inspiredd Jul 10 '21 at 12:43
  • It's because API :( variable is created in a right way. – inspiredd Jul 10 '21 at 12:47
  • I've added copy activity to the loop and try to iterate by values but I have an error : The action 'CopyIds' is nested in a foreach scope of multiple levels. Referencing repetition actions from outside the scope is supported only when there are no multiple levels of nesting – inspiredd Jul 10 '21 at 12:54
  • 1
    can you clarify what is the type of the output you are trying to construct, is it an array of values, or a single string of combined values? – Mutaz-MSFT Jul 12 '21 at 01:13

1 Answers1

1

If this is going to be static then you could just use an:

@replace(replace(replace(replace(string(variables('TestArray')), '{"name":', ''), '}', ''), '[', ''), ']', '')

The Pros: You don't have to use a ForEach, especially if there are hundreds of potential name fields in the output.

The Cons: This method is rigid so that it will only work if your output is always in this format.

See screenshots and comments below: Initial Output Final Output

The Quotes will always be escaped when viewing the string output, ADF automatically escapes all quote characters, but when the variable/output is actually used the escaped characters are ignored. Here is proof of this using the output as an additional column to write the output to a DB column: Screenshot 1 (Additional Column in Sink) Additional Column value being set Screenshot 2 (The mapping schema) DB Mapping Screenshot 3 (Showing the output does not contain escapes in the DB) Output from DB

Trent Tamura
  • 1,079
  • 6
  • 16
  • 1
    Add extra replace and split based on above formula, so that can store as a Array variable @split(replace(replace(replace(replace(replace(string(variables('TestArray')), '{"name":', ''), '}', ''), '[', ''), ']', ''),'"',''),',') – Kencourt Jul 22 '21 at 07:04
  • Yeah, wasn't sure if the OP wanted a string or array, but either way will work. – Trent Tamura Jul 22 '21 at 13:57