Postgresql: How do I use dynamic values when searching jsonb array of objects?-CodePudding

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.

CodePudding user response：

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]);

CodePudding user response：

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))
  `