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.
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]);
You cannot use parameters in a string literal.
Construct the jsonb object with PostgreSQL functions:
const findGameQuery = `
select playing
from users
where username = $1
and playing @> jsonb_build_array(jsonb_build_object('game', $2))
`