How to add new json object into json array in node-postgres?-CodePudding

I have a column named alerts with the type of json[] and I want to add json objects to this array such as {name: alert1, time: 12:00}.

I tried this code but it throws an error (error: malformed array literal: "$1")

await pool.query(
"UPDATE datas SET alerts = alerts || '$1' WHERE id = '10'",
[JSON.stringify({ name: "alert1", time: "12:00" })]);

How can I fix this issue?

CodePudding user response：

It's just matter of syntax errors, keys should be between double quotes and also in the call to pool.query parameter $1 doesn't need apostrophes

UPDATE datas SET alerts = alerts || ('{"name":"alert1", "time":"12:00"}')::json 
WHERE id = '10';

In your code

pool.query("UPDATE datas SET alerts = alerts || $1::json WHERE id = '10'", 
[{ "name": "alert1", "time": "12:00" }], (err, result) => {
    if (err) {
      return console.error('Error executing query', err.stack)
    }
    console.log(result.rowCount) // rows affected
  })