CODEWITHSUNDEEP
jsonpostgresqljsonbpostgresql-9.6
iffi
iffi

Reputation: 268

How to use `jsonb_set` on column with null values

I am using Postgres 9.6 and I have a JSONB column in which some rows have NULL value and some have dict values like {"notify": false}.

I want to update the column values with more dictionary key/value pairs.

UPDATE accounts SET notifications =  jsonb_set(notifications, '{"alerts"}', 'false');

Does work for the cases where I already have values like {"notify": false}. The end result becomes as expected {"alerts": false, "notifications": false}.

But the value I'm trying to update us NULL, nothing is updated in the db.

Can you give me any ideas how I can update the NULL values as well, so the end result for them will be values like {"notify": false}. The end result becomes as expected {"alerts": false}

Upvotes: 17

Views: 8980

Answers (2)

sql_knievel
sql_knievel

Reputation: 1369

Note that some versions of Postgres have coalesce() functions that don't support jsonb, and will give an error like this when trying to use the accepted answer:

ERROR:  function coalsece(jsonb, unknown) does not exist

You can work around that by using a case statement instead. Ugly, but it works.

UPDATE accounts 
SET notifications = 
  jsonb_set(
    case
      when notifications is null then '{}'
      else notifications
    end,
    '{"alerts"}','false')

Upvotes: 1

klin
klin

Reputation: 121574

Use coalesce():

UPDATE accounts 
SET notifications =  jsonb_set(coalesce(notifications, '{}'), '{"alerts"}', 'false')

or even simpler:

UPDATE accounts 
SET notifications =  coalesce(notifications, '{}') || '{"alerts": false}'

Upvotes: 33

Related Questions