Update table according with JSON/JSONB

Question:

I have a table books :

And a table book_modifications :

Now let's imagine I have a row in my books table like this :

id: 15,
name: "name-1",
nb_pages: 120

And a row to insert in my book_modifications table like this :

id: 7,
old_data: {name: "name-1", nb_pages: 120},
new_data: {name: "new-name-1"},
book_id: 15

My question is :

How can I update my books table with the new_data column of my book_modifications table in SQL or PostgreSQL ?

Currently I have found this way :

create or replace function on_book_modifications_insert () 
returns trigger 
language plpgsql 
as $$
begin

-- Here (new_data only contains the 'name' field)
UPDATE books 
SET name = new.new_data -> 'name', nb_pages = new.new_data -> 'nb_pages' 
WHERE id = new.book_id;

return new;

end;
$$

In this case, the SQL query above will update nb_pages to null because it is not present in new_data (which will generate an error), whereas I would like to leave nb_pages at 120 and only update fields present in new_data.

For INSERT, I've seen that json_object exists, but I can't find it for UPDATE.

Answer:

To achieve your goal of updating only the fields that are present in the new_data JSONB column without overwriting missing fields like nb_pages, you can make use of the jsonb_each_text function in PostgreSQL. This function allows you to extract keys and values from the new_data JSONB column dynamically, then update only those fields that are provided in the new_data.

Here’s how you can modify your trigger function to update only the fields that are present in new_data in dbForge Studio for PostgreSQL:

The jsonb_each_text(new.new_data) function iterates over the key-value pairs in new_data, and the for key, value in ... loop updates only the fields present in new_data using the execute format() function, ensuring that missing fields (like nb_pages) remain unchanged.