如何将变量添加到JSON路径中

Question

这是我使用的查询:

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

我得到这个错误:

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

我在node.js中使用node-postgres包。

如何解决这个问题?

Answer 1

已更新

所有这些驱动程序连接器都有自己的方法来做你正在寻找的事情。Node-postgres也有自己的

池

import { Pool } from 'pg';
const pool = new Pool({
  host: 'localhost',
user: 'database-user',
max: 20,
idleTimeoutMillis: 30000,
connectionTimeoutMillis: 2000,
});
/**
* execs the given sql statement.
* 
* @param {string} sql - query to run.
* @param {Array} params - an array with the parameter.
* @example
* runQuery("SELECT * FROM users WHERE id = $1", [1]).then(result=> console.log(result))
*/
export async function runQuery (sql, params) {
const connection = await pool.connect()
try {
await connection.query('BEGIN')
const queryText = 'INSERT INTO users(name) VALUES($1) RETURNING id'
const result = await connection.query(sql,params);

// check what result has
console.log(result);
return connection.query('COMMIT').then(result)
} catch (e) {
await connection.query('ROLLBACK')
throw e;
throw e
} finally {
connection.release()
}
}

池配置

config = {
// all valid client config options are also valid here
// in addition here are the pool specific configuration parameters:
// number of milliseconds to wait before timing out when connecting a new client
// by default this is 0 which means no timeout
connectionTimeoutMillis?: int,
// number of milliseconds a client must sit idle in the pool and not be checked out
// before it is disconnected from the backend and discarded
// default is 10000 (10 seconds) - set to 0 to disable auto-disconnection of idle clients
idleTimeoutMillis?: int,
// maximum number of clients the pool should contain
// by default this is set to 10.
max?: int,
}

结论

所以基本上一个查询的结构应该像或少于这个

const text = 'INSERT INTO users(name, email) VALUES($1, $2) RETURNING *'
const values = ['brianc', '[e-mail]']
connection
.query(text, values)
.then(res => {
console.log(res.rows[0])
// { name: 'brianc', email: '[e-mail]' }
})
.catch(e => console.error(e.stack))

Answer 2

使用括号符号代替点符号。所以不是obj。键使用obj[Key]