How to use `jsonb_set` on column with null values

user_1285576 0
Viewed 5738

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}

postgresql-9.6 jsonb postgresql json
2 Answers

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')
user_29698800
Related