Assign lookup result into array with better performance

user_10720117 0
Viewed 236

I have a look up which return json like below

{
    "count": 562,
    "value": [
        {
            "ID": 1,
        },
        {
            "ID": 2
        },

I would like to save it into a variable array of int, the only method I found is to loop the json value array with forearch and then append @item().ID to the array. Although this is working it will take over 1 minute for just 5xx records, which is far too slow.

Would like to know if there are method with better performance?

azure-data-factory-pipeline azure-data-factory-2
1 Answers

@Bill, Ofcourse this is not a direct answer as I couldnt find another way to do this withnin ADF. But if your Lookup is SQL, then you can return a comma seperated string as below and then convert it to an array.

select string_agg(t.DeviceId,', ') as DeviceIds
 from (select distinct DeviceId from DevicesTable where CustomerId = 1) as t

Output:

72, 127, 177, 200, 201
user_124513800
Related