1

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?

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
yksolanki9
  • 429
  • 2
  • 7
  • 14
  • 1
    Does this answer your question? [How to perform update operations on columns of type JSONB in Postgres 9.4](https://stackoverflow.com/questions/26703476/how-to-perform-update-operations-on-columns-of-type-jsonb-in-postgres-9-4) – Eyeslandic Sep 05 '22 at 08:51
  • also https://dba.stackexchange.com/questions/295298/how-to-update-a-property-value-of-a-jsonb-field – Eyeslandic Sep 05 '22 at 08:54
  • https://dba.stackexchange.com/questions/295298/how-to-update-a-property-value-of-a-jsonb-field @Eyeslandic the accepted answer for this question works only if your field is of type json, but like I mentioned, I'm dealing with text type field here – yksolanki9 Sep 05 '22 at 11:43
  • https://stackoverflow.com/questions/26703476/how-to-perform-update-operations-on-columns-of-type-jsonb-in-postgres-9-4 @Eyeslandic this mentions updating the entire json object which I'm already aware of, but that is not what I asked for – yksolanki9 Sep 05 '22 at 11:44

1 Answers1

1

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}
Adrian Klaver
  • 15,886
  • 2
  • 17
  • 28