How to add $1 into json path in the query?

Question

This is the query I am using:

app.get("/items/:data", async (req, res) => {

    const { data } = req.params;

    query = `
        SELECT items.discount
        FROM items
        WHERE items.discount @? '$[*] ? (@.discount[*].shift == $1)'
        `
    try {

        const obj = await pool.query(query, [data]);
        res.json(obj.rows[0])

    } catch(err) {

        console.error(err.message);

    }
});

I get this error:

error: bind message supplies 1 parameters, but prepared statement "" requires 0

I am using node-postgres package in node.js.

How can I solve this issue?

Answer 1

Inside quotes, the placeholder is not a placeholder, it is just two characters, a dollar sign and a digit. You would either have to construct the whole jsonpath in the client and send it in as one parameter:

WHERE items.discount @? $1

Or piece it together from strings in SQL with, for example, the concatenation operator:

WHERE items.discount @? ('$[*] ? (@.discount[*].shift == '||$1||')')::jsonpath

You need the parenthesis to get the priority correct, and you need the explicit cast because once you assemble it from text it will no longer implicitly cast it to jsonpath.

You should carefully consider if either or both of these opens you up to injection attacks if the provided data does not look like what you thought it would. The use of the parameter should protect you from SQL injection attacks, but not inherently from jsonpath injection.