Why jsonb-functions does not work on update when column is null?

user_196241 0
Viewed 354

I have a table with a jsonb column, where the column default value is null. Now when I try to update this column with data first time using jsonb_set or jsonb_insert I get UPDATE 1 as a result but actually, the column stays null.

I made little test-case:

CREATE SCHEMA test;
CREATE TABLE test.books (  
  book_id serial NOT NULL,
  data jsonb
);

INSERT INTO test.books (data) VALUES (null);
INSERT INTO test.books (data) VALUES ('{}');

Now I update both new records:

UPDATE test.books SET data = jsonb_set( data, '{zzz}', 'true', true) WHERE book_id = 1;
UPDATE test.books SET data = jsonb_set( data, '{zzz}', 'true', true) WHERE book_id = 2;

And result:

# select * from test.books;
 book_id |                data                
---------+------------------------------------
       2 | {"zzz": true}
       1 | 
(2 rows)

Even when the update yielded the same result for both updates, the first row remained untouched.

I looked into documentation but found nothing about such behavior. Why it does not work this way?

I am using Postgres 9.6

jsonb postgresql
2 Answers

This is because jsonb_set() is defined as strict which means if any input parameter is null the result is null.

You will need to use coalesce() in your call to jsonb_set:

UPDATE books 
   SET data = jsonb_set( coalesce(data, '{}'), '{zzz}', 'true', true) 
WHERE book_id = 1;

Note that this is true for any of the parameters of the function.
So jsonb_set(data, '{zzz}', null, true) would also yield null even if data is not null.

user_3303150

What worked for me to set a native JSON value of null within a JSONB variable was the following:

IF condition = 1 THEN
    response = jsonb_set(
                    jsonb_in => response::jsonb,
                    path => ('{response, data, my_key')::text[],
                    replacement => 'null'::jsonb,
                    create_if_missing => true
                );
END IF;
user_165603970
Related