如何在node-postgres的JSON路径查询中插入变量并解决参数绑定错误

写在单引号包裹的JSON路径字符串内部的$1不会被PostgreSQL识别为准备语句的参数占位符，会被判定为JSON路径语法的一部分，因此PostgreSQL认为你的查询没有定义任何参数，传入1个参数就触发了绑定数量不匹配的报错。

推荐使用PostgreSQL原生的JSON路径查询函数jsonb_path_exists（对应json类型用json_path_exists）替代@?操作符，该函数支持显式传入动态参数，不会出现参数绑定失效问题：

app.get("/items/:data", async (req, res) => {
    const { data } = req.params;
    const query = `
        SELECT items.discount
        FROM items
        WHERE jsonb_path_exists(
            items.discount,
            '$[*] ? (@.discount[*].shift == $input)',
            jsonb_build_object('input', $1::text)
        )
    `
    try {
        const obj = await pool.query(query, [data]);
        res.json(obj.rows[0] ?? {})
    } catch(err) {
        console.error(err.message);
        res.sendStatus(500)
    }
});

方案说明 #

• 如果你的discount字段是json类型而非jsonb类型，把函数换成json_path_exists、json_build_object即可
• JSON路径中的$input是自定义变量名，和jsonb_build_object里的key保持一致即可
• 如果你的shift字段是数字类型，可以把$1::text改为$1::int做类型匹配

不推荐直接拼接JSON路径字符串的方式，会存在SQL注入风险，仅在你对传入的data参数做了严格的白名单/格式校验的场景下可酌情使用。

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