I am currently trying to build a query for finding specfic object within a jsonb array. I have the following query which works fine if I used a hard coded string for the “game” value e.g.
const findGameQuery = ` select playing from users where username = $1 and playing @> '[{"game": "new-pokemon-snap"}]' `
However, if I use a dynamic value like I currently do for username, I get invalid json syntax error. e.g.
const findGameQuery = ` select playing from users where username = $1 and playing @> '[{"game": $2}]' ` const { rows } = await query(findGameQuery, [username, game]); ctx.body = rows
How do I search using a dynamic value here? I have done a ton of searching and can’t find any examples. $2 value is just a string so not sure why not accepted.
Advertisement
Answer
When you send this query, it only has ONE parameter:
select playing from users where username = $1 and playing @> '[{"game": $2}]'
The correct query is:
select playing from users where username = $1 and playing @> $2
You have to make the array with the object in the parameter.
const gameObj = [{ "game": game }]; const gameParam = JSON.stringify(gameObj); const { rows } = await query(findGameQuery, [username, gameParam]);