André Bastos André Bastos - 8 months ago 79
Node.js Question

Update multiple jsonb values

I'm trying to create a cron to run every month to update some keys of a jsonb field, in a ndoe application with postgres database.

In my database I have a jsonb field with keys like "disk_alert", "temepratures_alert", "consumptions_alert" and I want to do something like

Postgres:

UPDATE devices SET
data=jsonb_set(data::jsonb,'{disk_alert}','false'::jsonb,true),
modified_date=NOW()
WHERE id=$1 AND NOT deleted;


Node:

client.query("UPDATE devices SET data=jsonb_set(data::jsonb,'{disk_alert}','false'::jsonb,true), modified_date=NOW() WHERE id=$1 AND NOT deleted", [deviceId]


But how to add the other keys "consumptions_alert" and "temperatures_alert" ?? I read something about to use cocnatenation || operator but how to do this?

Answer Source

You can use the concatenation operator in the way like this:

UPDATE devices SET 
    data = data::jsonb || '{"disk_alert": false, "temepratures_alert": false}',
    modified_date = NOW() 
WHERE id=$1 AND NOT deleted;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download