When I have a list of values in one variable you can pass them to a where clause using something like this:

SET LIST = '["LIST1","LIST2"]';

SELECT * FROM TABLE WHERE FILTER IN (SELECT VALUE FROM TABLE(FLATTEN(INPUT => PARSE_JSON($LIST))))

I would like to do the same thing using ILIKE ANY but it only allows you to return one row from the selection

SET LIST = '["%LIST1%","%LIST2%"]';
SELECT * FROM TABLE WHERE FILTER ILIKE ANY (SELECT VALUE FROM TABLE(FLATTEN(INPUT => PARSE_JSON($LIST))))

Error: Single-row subquery returns more than one row.

So I need some way of flattening the list further but still allowing it to be passed to the ILIKE ANY() function.

The end result would be the below but using the variable $LIST

SELECT * FROM TABLE WHERE FILTER ILIKE ANY('%LIST1%','%LIST2%')