I have a column address in students table which actually stores a JSON object but it is of type text for legacy reasons. Now, to get the value of any of these keys in the json object, I use postgres type cast :: operator with json -> operator as shown
select address::jsonb->'pincode' from students where id='xyz';
This query returns the correct pincode for the student with id xyz. Now lets say, I want to update the pincode for a student, and now, if I do something like
update students set address::jsonb->'pincode'='182741' where id='abc';
I get this error
ERROR: syntax error at or near "::"
I know I can overwrite the entire column value but that is a lot to do if you just want to update one key. Can anyone help me understand why we cannot use the type cast operator here? Any what is the correct way to update only one key in the JSON object?
Use jsonb_set from here JSON functions.
create table students (id integer, address varchar);
insert into students values (1, '{"name": "Adrian", "pincode": 1234}');
select address::jsonb->'pincode' from students where id=1;
1234
update students set address = jsonb_set(address::jsonb, '{pincode}', '182741') where id =1;
select * from students;
id | address
----+---------------------------------------
1 | {"name": "Adrian", "pincode": 182741}