Search code examples
javascriptnode.jspostgresqlnode-postgres

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


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?


Solution

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