PostgreSQL rename attribute in jsonb field

Question

In postgresql 9.5, is there a way to rename an attribute in a jsonb field?

For instance:

{ "nme" : "test" } 

should be renamed to

 { "name" : "test"} 

Answer 1

In UPDATE use the delete (-) and concatenation (||) operators , for example:

 create table example(id int primary key, js jsonb); insert into example values (1, '{"nme": "test"}'), (2, '{"nme": "second test"}'); update example set js = js - 'nme' || jsonb_build_object('name', js->'nme') where js ? 'nme' returning *; id | js ----+------------------------- 1 | {"name": "test"} 2 | {"name": "second test"} (2 rows) 

Answer 2

I used the following to handle nested attributes and skip any json that doesn't use the old name:

 UPDATE table_name SET 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'; 

Answer 3

My variable type is JSON. When I changed it using JSONB, I cannot do this because it depends on the view. Anyway, I fixed it with column_name :: jsonb. I would like to share with people who meet like this problem. Also thanks @klin