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

Question

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?

Pepe N O · Accepted Answer · 2022-10-12 17:32:50Z

1

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

edited Oct 12, 2022 at 17:32

answered Oct 12, 2022 at 15:00

Pepe N O

2,3841 gold badge12 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Jantoma21 Over a year ago

I changed my code to this await pool.query("UPDATE datas SET alerts = alerts || '$1'::json WHERE id = '10'",[JSON.stringify({ "name": "alert1", "time": "12:00" })]); now it says "error: invalid input syntax for type json" but without ::json it still throws the same error

Pepe N O Over a year ago

You're right, now I corrected and tested successfully.

Jantoma21 Over a year ago

hello, it is kinda off topic but what about if I want to replace the array with new array? (so not adding any item this time, I need to replace the array with new array) I tried this but did not work: await pool.query("UPDATE datas SET alerts = '{}'::json[] || $1::json[]", [JSON.stringify(alerts).replaceAll('[', '{').replaceAll(']', '}')]); this throws an error => error: malformed array literal: "{{"option":"ns=3;s=\"random_out\"","minOrEqual":"10000","maxOrEqual":"120000","frequencyTime":"100000","lastSendedTime":"2022-10-12T20:49:26.396Z"}}" any idea how to fix this?

Pepe N O Over a year ago

For a new array (not appended), like this pool.query("UPDATE datas SET alerts = $1::json[] WHERE id = '10'", [[{ "name": "alert1 from node", "time": "12:38" }]] requires another pair of square brackets to express array. But if you want to clear the field is update datas set alerts =null where id='10'

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related