PostgreSQL rename attribute in jsonb field
In UPDATE use delete (-) and concatenate (||) operators, e.g.:
create table example(id int primary key, js jsonb);insert into example values (1, '{"nme": "test"}'), (2, '{"nme": "second test"}');update exampleset js = js - 'nme' || jsonb_build_object('name', js->'nme')where js ? 'nme'returning *; id | js ----+------------------------- 1 | {"name": "test"} 2 | {"name": "second test"}(2 rows)
I used the following for handling nested attributes and skipping any json that doesn't use the old name:
UPDATE table_nameSET json_field_name = jsonb_set(json_field_name #- '{path,to,old_name}', '{path,to,new_name}', json_field_name#>'{path,to,old_name}')WHERE json_field_name#>'{path,to}' ? 'old_name';just for reference docs:
#-Delete the field or element with specified path (for JSON arrays, negative integers count from the end)https://www.postgresql.org/docs/current/functions-json.html#FUNCTIONS-JSONB-OP-TABLE#>Get JSON object at the specified path https://www.postgresql.org/docs/current/functions-json.html#FUNCTIONS-JSON-OP-TABLE