cant remove quotes using replace in the data factory pipeline expressions

Question

so I have a lookup which outputs { "firstRow": { "result": "[\"0A09\",\"0B01\",\"0B02\",\"0B04\"],[\"0F78\",\"0F79\",\"0F80\"]" } I want to set the output to a variable in a array form so I can use the output in a for each loop.

I want [[\"0A09\",\"0B01\",\"0B02\",\"0B04\"],[\"0F78\",\"0F79\",\"0F80\"]]

problem is when I use @createArray(activity('Lookup1').output.firstRow.result) I get value ["[\"0A09\",\"0B01\",\"0B02\",\"0B04\"],[\"0F78\",\"0F79\",\"0F80\"]"] this is treating the two list as one when i run it through for each loop.

I have tried to use replace function to remove " but it is not working. i.e @createArray(replace(activity('Lookup1').output.firstRow.result,'"[','[')) This should ahve removed " and given me [[\"0A09\",\"0B01\",\"0B02\",\"0B04\"],[\"0F78\",\"0F79\",\"0F80\"]"]

but its not, i am still getting the quote. anyone knows why is it not working

i have also tried@createArray(replace(activity('Lookup1').output.firstRow.result,'\"[','['))
and still getting output like ["[\"0A09\",\"0B01\",\"0B02\",\"0B04\"],[\"0F78\",\"0F79\",\"0F80\"]"]

Answer 1

I want [[\"0A09\",\"0B01\",\"0B02\",\"0B04\"],[\"0F78\",\"0F79\",\"0F80\"]]

You can use json() function to generate the required array from the string.

This is my lookup output as same as yours:

Use the below expression to convert the above string into a two dimensional array.

@json(concat('[',activity('Lookup1').output.firstRow.result,']'))

Here, for sample I have used it in an array variable. You can directly give this expression in the ForEach activity.

Output array: