I have a table T with a column J of type json, and one of it's row contains value
{'SELECTION':'A'}
I want to change it to
{'selection':'A'}
i.e change the key of the JSON.
Any one has experience? I am not able to find any relevant resource.
-
2Does this answer your question? PostgreSQL rename attribute in jsonb fieldSebastian Brosch– Sebastian Brosch2020-01-25 11:16:01 +00:00Commented Jan 25, 2020 at 11:16
-
let me try, actually my column type is json, not jsonbAkshay Vijay Jain– Akshay Vijay Jain2020-01-25 11:22:06 +00:00Commented Jan 25, 2020 at 11:22
Add a comment
|
2 Answers
Thanks to @Sebastian, I solved it using the documentation https://www.postgresql.org/docs/current/functions-json.html
update T set J = '{"selection":"A"}' where J::jsonb @> '{"SELECTION":"A"}'::jsonb
Another query that will do same
update T set J = '{"selection":"A"}' where J::json->>'SELECTION'='A'
Comments
For me this worked well.
create table form
(
id integer,
data json
);
insert into form (id, data)
values (1000,
$$
{"formType": "DEFAULT", "old_key": {"column": [{"old_key": "1"}, {"old_key": "7"}, {"old_key": "22"}]}}
$$::json
)
;
SELECT *
FROM form;
UPDATE form
set data = data::jsonb - 'old_key' || jsonb_build_object('new_key', data->'old_key')
where data::jsonb ? 'old_key';
SELECT *
FROM form;
