SQL query using IN Operator

Community Help
1

I am using a notebook, and I have an array, but I am struggling to use the SQL query. So the list_of_tx is the array of values.

I have look at the markdown, and the SQL queries looks fine to me, but I just not able to use the SQL query functionality on the notebook. It is returning empty values.

SELECT *
FROM table
WHERE txn IN (${list_of_tx.map(tx => `'${tx}'`).join(', ')})

Anyone here able to help?

2

What SQL dialect are you using? Only Postgres and MySQL have proper support for interpolated array values. For any other RDBMS (including DuckDB) you may want to take a look at Mixing Queries and Arrays into SQL / Observable | Observable

3

I was using Postgres.

Observable doesn’t seem to like it when I tried to create a let variable to update the array. So, I have been having trouble in doing that.

What I was trying to achieve was to set some if conditions, and if true, add extra element to the array. Then used that array in the IN operators to filter the DB.

4

Writing code in an SQL cell behaves roughly the same as if you were writing the following code in a JS cell:

(await DatabaseClient("my-connector-name")).sql`
 SELECT ...
`

Any interpolated values are replaced by placeholders ($1, $2 etc), so you can’t (and shouldn’t :slightly_smiling_face:) interpolate actual SQL.

Luckily the node-postgres client that the connector uses internally supports array interpolation, so all you need to write is:

SELECT *
FROM table
WHERE txn = ANY (${list_of_tx})
5

Thanks the ANY was useful.