jsonb_replace_recursively.sql

CREATE FUNCTION jsonb_replace_by_key(obj jsonb, search text, substitute jsonb) RETURNS jsonb
STRICT LANGUAGE SQL AS $$
  SELECT CASE jsonb_typeof(obj)
    WHEN 'object' THEN
      (SELECT coalesce(jsonb_object_agg(key, CASE WHEN key = search
          THEN substitute
          ELSE jsonb_replace_by_key(value, search, substitute)
        END), '{}'::jsonb)
      FROM jsonb_each(obj))
    WHEN 'array' THEN
      (SELECT coalesce(jsonb_agg(jsonb_replace_by_key(el, search, substitute)), '[]'::jsonb)
      FROM jsonb_array_elements(obj) el)
    ELSE
      obj
 END;
$$;

-- Exammple usage

create table test
(
    id int primary key,
    js jsonb
);
insert into test values (1, '{"id": "total", "price": null, "breakdown": [{"id": "product1", "price": 400}] }');
insert into test values (2, '{"id": "total", "price": 1000, "breakdown": [{"id": "product1", "price": 400}, {"id": "product2", "price": 600}]}');

update test set js = jsonb_replace_by_key(js, 'price', 'null'::jsonb);

select * from test;