How to add variable into JSON path

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

Use bracket notation instead of dot notation. So instead of obj.key use obj[key]

Answer 2

Updated

all them driver connectors come with their own method to do what you're looking for. node-postgres also have there own

Pool

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

Pool Config

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,
}

conclution

so basically the structure of a query should be like or less this

const text = 'INSERT INTO users(name, email) VALUES($1, $2) RETURNING *'
const values = ['brianc', '[email protected]']

connection
  .query(text, values)
  .then(res => {
    console.log(res.rows[0])
    // { name: 'brianc', email: '[email protected]' }
  })
  .catch(e => console.error(e.stack))