Recently, I stumbled upon a use case where I need to partially update the JSON column.
What has not worked for me
Set using ->> operator
I thought I could do something along those lines before realizing it doesn't work 😞
UPDATE task SET data->>'status' = 'completed';
Using json_set and jsonb_set functions
Upon a quick Google search, I found that Postgres has a json_set
function to partially set json values, But I always get the "function does not exist" error.
What has worked for me
I found a workaround which is using string replacement instead. Take a look at the example:
UPDATE task SET data = replace(data::text, '"status":"pending"', '"status":"completed"')::json;
How it is working
We are using the Postgres replace
function to replace a string. Postgres supports typecasting. We are converting JSON field to string, replacing the required text, converting it back to JSON, and updating the entire data column with the new values.
Note
This works perfectly for my use case but you have to take into consideration eg speed and chances of data inconsistency while replacing the string.
Enjoy
Top comments (0)