Pass array variable to IN condition in ADF

I need to be able to query my Azure database (Currently in Azure Synapse Dedicated Pool), return a list of items, and then pass that list of items to query from a source database. The number of values from my initial query could be anywhere from 1 to 200. I know that I could technically pass these to a for-each and query them one at a time, but was trying to be more efficient with my query to the source.

I am currently doing a lookup to select the value I need:

select ID as ID from listTable

I am then trying to set that to a variable for the query using

@concat('select ID from source where ID in (',activity('Lookup1').output.value,')')

When I do this it works, but the query is including both the ID label and the value.

select ID from source where ID in ([{\"ID\":\"ffce9c3e476a9d98f30db348436d43a4\"},{\"ID\":\"3d838131c32de954e47c166ce0013135\"},{\"ID\":\"561f43c8db3885543cc1892d139619c0\"},{\"ID\":\"88e69e5e97d359143e5b37d11153af1d\"},{\"ID\":\"e4a21fd4974c6d9ce41a78511153afb2\"},{\"ID\":\"bc699661978da9949586fe021153af75\"},{\"ID\":\"97aaaada47ad2d105a53bb45d36d4300\"},{\"ID\":\"4422d05fc3c2d5506ae3963ce0013174\"},{\"ID\":\"6a76aa3e9749a510e41a78511153af34\"},{\"ID\":\"63f129da47192d50f30db348436d4360\"}])

So I need to only get the 2nd portion of the array, and to somehow format it so that it will be a valid IN statement.

You can use open_json() to get the columns from the JSON array. Try below script in either lookup activity query or script activity to get the desired result.

declare @json nvarchar(max) = N'@{activity('get IDS list lookup').output.value}';
select Id from sample2 where Id in (SELECT Id
FROM OPENJSON(@json) WITH (
   Id varchar(max)
));

This is my first lookup output array:

[
        {
            "Id": "24"
        },
        {
            "Id": "26"
        },
        {
            "Id": "1"
        },
        {
            "Id": "2"
        },
        {
            "Id": "10"
        },
        {
            "Id": "4"
        },
        {
            "Id": "18"
        },
        {
            "Id": "17"
        },
        {
            "Id": "7"
        }
]

SQL table rows:

I have used above query in lookup like below.

I have stored the result lookup object array in a set variable.

Result: