Postgresql查询jsonb对象数组时如何使用动态值？

你使用的写法中，'[{"game": $2}]' 是被包裹在单引号内的字符串常量，PostgreSQL 会直接将这段内容当做 JSON 字符串解析，不会识别其中的 $2 为参数占位符：

• 语法层面，未加引号的 $2 不符合 JSON 字符串的格式要求，因此会直接抛出 JSON 语法错误
• 即使修复引号问题，占位符也不会被替换，只会被当做字面量字符串 $2 处理

方案1：使用 PostgreSQL 内置 JSONB 构造函数（推荐） #

通过 jsonb_build_object 和 jsonb_build_array 动态构造要匹配的 JSONB 结构，参数占位符直接写在 SQL 语法的非字符串区域，可被正常识别：

const findGameQuery = `
  select playing
  from users
  where username = $1
  and playing @> jsonb_build_array(jsonb_build_object('game', $2))
`
const { rows } = await query(findGameQuery, [username, game]);
ctx.body = rows

方案2：在 JS 层构造完整匹配对象传入 #

你也可以直接在代码层构造好要匹配的完整 JSON 结构，作为参数直接传入，声明为 JSONB 类型即可：

const findGameQuery = `
  select playing
  from users
  where username = $1
  and playing @> $2::jsonb
`
// 构造要匹配的数组对象作为参数
const matchObj = [{ game: game }]
const { rows } = await query(findGameQuery, [username, matchObj]);
ctx.body = rows

这种写法更适合匹配结构比较复杂、字段较多的场景。

内容的提问来源于stack exchange，提问作者George