Array in where clause IN()

Queries and Resources
1

I’ve got a query

SELECT
  *
FROM
  table
WHERE
  product_id IN ({{ "'"+transformer.value.join("','")+"'" }})

But I can't get it to work. transformer.value returns an array of strings. The strings look like this: gid://shopify/Product/8492752863571. See the screenshot below:

Screenshot 2024-09-24 at 23.02.41

In the database I know there's a row with column product_id set to gid://shopify/Product/8492752863571. But still I get zero results.

When I copy the actual query it does give me 3 results:

SELECT
  *
FROM
  table
WHERE
  product_id IN ('gid://shopify/Product/5946903756950',
'gid://shopify/Product/7964207579362',
'gid://shopify/Product/7964208038114','gid://shopify/Product/7964209545442',
'gid://shopify/Product/7964209938658','gid://shopify/Product/7964211478754',
'gid://shopify/Product/7964211544290','gid://shopify/Product/7964211904738'......

seems like nothing gives me results except by running the query without {{ }} variables and just write the query by hand like here above. Anyone got a solution, alternative approach or more insight in what I'm doing wrong?

2

The way that the resources are using prepared statements makes this a little less straightforward. You can create the entire query as a return string in a JS Query and then DECLARE a nvarchar (MAX) variable and SET that to be the returned string and finally EXEC the variable.

For the purposes of making just an IN clause, I have begun using the string_split SQL method to handle the proper formatting. For your statement it might be something like:

SELECT
  *
FROM
  table
WHERE
  product_id IN ( SELECT * FROM string_split( {{ transformer.value.toString() }}, ',') )
1 Like
3

Thanks! Using Retool Database so it needs to be postgresql. The Postgresql version below works though.

Postgresql:

SELECT
  *
FROM
  table
WHERE
  product_id IN (
    SELECT
      unnest(string_to_array({{transformer.value.toString()}}, ','))
  );

Good start of the day :coffee:

1 Like