How to add "x in y" clause in ExpressJS

Question

In Postgres I have created a simple table called tags with these columns:

tag_id
tag
owner_id

In ExpressJS, this query works fine:

return pool.query(`SELECT tag_id, tag FROM tags WHERE owner_id = $1`, [ownerId]);

Now what I want to do is restrict which tags are returned via an array of tag values I'm passing in:

const tagsCsv = convertArrayToCSV(tags);  // Example: "'abc','def'"
return pool.query(`SELECT tag_id, tag FROM tags WHERE owner_id = $1 AND tag IN ($2)`, [ownerId, tagsCsv]);

The code doesn't crash but it returns an empty array when I know for a fact that both abc & def are sample tags in my table.

I thus suspect something is wrong with my syntax but am not sure what. Might anyone have any ideas?

Robert

try `[ownerId, tags]` IN expects an array of items not a string — Lawrence Cherone, Dec 09 '22 at 15:05

score 0 · Answer 1 · answered Dec 09 '22 at 15:14

I did more searching and found this: node-postgres: how to execute "WHERE col IN (<dynamic value list>)" query?

Following the examples in there, I stopped converting the string array to a CSV string and instead did this:

const tags: Array<string> = values.tags;
return pool.query(`SELECT tag_id, tag FROM tags WHERE owner_id = $1 AND tag = ANY($2::text[])`, [ownerId, tags]);

This worked perfectly, returning the records I was expecting!

How to add "x in y" clause in ExpressJS

1 Answers1