How to use `jsonb_set` on column with null values

Question

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}

Answer 1

Use coalesce():

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

or even simpler:

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