Partial Update JSON Column in Postgres

#postgres

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

Hands-on debugging session: instrument, monitor, and fix

Join Lazar for a hands-on session where you’ll build it, break it, debug it, and fix it. You’ll set up Sentry, track errors, use Session Replay and Tracing, and leverage some good ol’ AI to find and fix issues fast.

RSVP here →