1

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?

AlexElin
  • 1,044
  • 14
  • 23
Cátia Matos
  • 820
  • 1
  • 8
  • 26

1 Answers1

1

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;
klin
  • 112,967
  • 15
  • 204
  • 232
  • This raises the error "{ error: column "data" is of type json but expression is of type text" I'm trying to do UPDATE devices SET data = data || {'disk_alert': false, 'temperatures_alert': false, 'ram_alert': false, 'cpu_alert':false}', modified_date=NOW() WHERE id=$1 AND NOT deleted – Cátia Matos Oct 03 '17 at 10:36
  • If the column `data` is of type json then cast it to jsonb, like in the updated answer. – klin Oct 03 '17 at 10:45