Postgres 9.5

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE OR REPLACE FUNCTION jsonb_replace_value(_j jsonb, _old jsonb, _new jsonb)
   RETURNS jsonb AS
$func$
DECLARE
   _key text;
   _val jsonb;
BEGIN
   FOR _key, _val IN
      SELECT * FROM jsonb_each(_j)
   LOOP
      IF _val = _old THEN
         RETURN jsonb_set(_j, ARRAY[_key], _new);  -- update 1st key
      END IF;
   END LOOP;

   RETURN _j;  -- nothing found, return original
END
$func$ LANGUAGE plpgsql IMMUTABLE;

COMMENT ON FUNCTION jsonb_replace_value(jsonb, jsonb, jsonb) IS 'Replace the first occurrence of _old value with _new.';
CREATE TABLE table_a (id int, json_col jsonb);
INSERT INTO table_a VALUES
  (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"}')        -- both
, (4, '{"C1":"Paris","C2":"London","C3":"No-Berlin","C4":"Ankara", "C30":"Madrid"}')     -- no Berlin
, (5, '{"C1":"Paris","C2":"London","C3":"Berlin","C4":"Ankara", "C30":"Berlin"}')        -- 2 Berlin
, (6, '{"C1":"Paris","C2":"London","C3":"No-Berlin","C4":"Ankara", "C30":"No-Madrid"}'); -- neither
6 rows affected

CREATE OR REPLACE FUNCTION jsonb_object_val_arr(jsonb)
   RETURNS text[] LANGUAGE sql IMMUTABLE AS
'SELECT ARRAY (SELECT value FROM jsonb_each_text($1))';

COMMENT ON FUNCTION jsonb_object_val_arr(jsonb) IS '
   Generates text array of values in outermost jsonb object.
   Of limited use if there can be nested objects.';

CREATE INDEX table_a_val_arr_idx ON table_a USING gin (jsonb_object_val_arr(json_col));
-- smart SELECT
SELECT a.id, jsonb_replace_value(a.json_col, '"Berlin"', '"Madrid"')
FROM   table_a a
WHERE  jsonb_object_val_arr(json_col) @> '{Berlin}' -- has Berlin, possibly > 1x ..
AND    NOT EXISTS (                                 -- .. but not Madrid
   SELECT FROM table_a b
   WHERE  jsonb_object_val_arr(json_col) @> '{Madrid}'
   AND    b.id = a.id
   );


id
jsonb_replace_value


1
{"C1": "Paris", "C2": "London", "C3": "Madrid", "C4": "Tokyo", "C30": "Dallas"}

2
{"C1": "Dublin", "C2": "Madrid", "C3": "Kiev", "C4": "Tokyo", "C30": "Phoenix"}

5
{"C1": "Paris", "C2": "London", "C3": "Madrid", "C4": "Ankara", "C30": "Berlin"}


/* -- primitive UPDATE
UPDATE table_a a
SET    json_col = jsonb_replace_value(a.json_col, '"Berlin"', '"Madrid"');
*/

-- smart UPDATE
UPDATE table_a a
SET    json_col = jsonb_replace_value(a.json_col, '"Berlin"', '"Madrid"')
WHERE  jsonb_object_val_arr(json_col) @> '{Berlin}' -- has Berlin, possibly > 1x ..
-- AND    NOT jsonb_object_val_arr(json_col) @> '{Madrid}'
AND    NOT EXISTS (                                         -- .. but not Madrid
   SELECT FROM table_a b
   WHERE  jsonb_object_val_arr(json_col) @> '{Madrid}'
   AND    b.id = a.id
   )
RETURNING *;


id
json_col


1
{"C1": "Paris", "C2": "London", "C3": "Madrid", "C4": "Tokyo", "C30": "Dallas"}

2
{"C1": "Dublin", "C2": "Madrid", "C3": "Kiev", "C4": "Tokyo", "C30": "Phoenix"}

5
{"C1": "Paris", "C2": "London", "C3": "Madrid", "C4": "Ankara", "C30": "Berlin"}

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