Using SQL IN statement within Query Library

Queries and Resources
1

I am trying to use an IN statement in a WHERE clause in the Query Library.

I've written the query and am testing in the Query Library interface where you can enter the variables on the right hand side.

The query in principle looks like
SELECT * FROM TABLE WHERE id IN({{ ids }}

I have confirmed as pure SQL that the ids I am entering do return results.

Initially I tried putting the ids as an actual array in the variable
e.g. ["0058d000001aRaSAAU","0058d000006YPY7AAO"]
Query Library recognises this as an array but produces an empty result. Using the button to view the output SQL I can see it is missing the single quotes needed.

So then I tried to map the data to add the quotes back in
SELECT * FROM TABLE WHERE id IN({{ ids.map((x) => `"${x}"`) }})

Frustratingly this produces the correct SQL but still doesn't work.

I've also tried just using the correct string as the variable e.g.
'0058d000001aRaSAAU','0058d000006YPY7AAO'
This also produces the correct SQL but does not return any results.

Not had any luck using integers either.

Anybody figured out how to use an IN statement with a variable?

2

Hello @Ross_Coombes, I understand your issue. For your query, you can use the following query:

SELECT
*
FROM
TABLE
WHERE
id IN ({{ id.map(id => '${id}').join(', ') }});

For perform static query, you can use this query :
SELECT
*
FROM
TABLE
WHERE
id IN ('0058d000001aRaSAAU', '0058d000006YPY7AAO');

1 Like
3

If you are passing an array to a Postgres DB, you don't want to use IN you want to use = ANY() along the lines of select * from table where id = ANY({{array}}).

You can certainly use the .map() approach as well, but putting this here in case others come across the question.

4

Thank you @ZeroCodez for showing me the correct way to use the join method.

I think ANY instead of IN is the elegant solution here for future people that find this thread. Thanks @jg80

2 Likes