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}

Did you try `jsonb_set(coalesce(notifications,'{}'), '{"alerts"}', 'false')` — , Jul 07 '17 at 13:17

score 31 · Accepted Answer · answered Jul 07 '17 at 13:24

31

Use coalesce():

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

or even simpler:

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

answered Jul 07 '17 at 13:24

klin

112,967
15
204
232

score 1 · Answer 2 · answered Jul 19 '21 at 14:04

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

*some versions* - in all supported versions (as of today 9.6+) `coalesce()` works well with `jsonb`. — klin, Nov 22 '21 at 13:56

How to use `jsonb_set` on column with null values

2 Answers2