Conditionally replace single value per row in jsonb column

Question

I need a more efficient way to update rows of a single table in Postgres 9.5. I am currently doing it with pg_dump, and re-import with updated values after search and replace operations in a Linux OS environment.

table_a has 300000 rows with 2 columns: id bigint and json_col jsonb. json_col has about 30 keys: "C1" to "C30" like in this example:

Table_A

    id,json_col
    1  {"C1":"Paris","C2":"London","C3":"Berlin","C4":"Tokyo", ... "C30":"Dallas"}
    2  {"C1":"Dublin","C2":"Berlin","C3":"Kiev","C4":"Tokyo", ... "C30":"Phoenix"}
    3  {"C1":"Paris","C2":"London","C3":"Berlin","C4":"Ankara", ... "C30":"Madrid"}
    ...

The requirement is to mass search all keys from C1 to C30 then look in them for the value "Berlin" and replace with "Madrid" and only if Madrid is not repeated. i.e. id:1 with Key C3, and id:2 with C2. id:3 will be skipped because C30 exists with this value already

It has to be in a single SQL command in PostgreSQL 9.5, one time and considering all keys from the jsonb column.

Answer 1

The fastest and simplest way is to modify the column as text:

update table_a
set json_col = replace(json_col::text, '"Berlin"', '"Madrid"')::jsonb
where json_col::text like '%"Berlin"%'
and json_col::text not like '%"Madrid"%'

It's a practical choice. The above query is rather a find-and-replace operation (like in a text editor) than a modification of objects attributes. The second option is more complicated and surely much more expensive. Even using the fast Javascript engine (example below) more formal solution would be many times slower.

You can try Postgres Javascript:

create extension if not exists plv8;

create or replace function replace_item(data jsonb, from_str text, to_str text)
returns jsonb language plv8 as $$
    var found = 0;
    Object.keys(data).forEach(function(key) {
        if (data[key] == to_str) {
            found = 1;
        }
    })
    if (found == 0) {
        Object.keys(data).forEach(function(key) {
            if (data[key] == from_str) {
                data[key] = to_str;
            }
        })
    }
    return data;
$$;

update table_a
set json_col = replace_item(json_col, 'Berlin', 'Madrid');