CODEWITHSUNDEEP
azure-data-factory
Bill
Bill

Reputation: 540

Assign lookup result into array with better performance

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?

Upvotes: 1

Views: 1150

Answers (1)

SriramN
SriramN

Reputation: 501

@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

Upvotes: 3

Related Questions