Alter single value in json string in text field

Question

I have a table with one column of type text which contains a json string. I would like to do a query where I select a bunch of rows (around 50) and for these rows update a single value in the json that is saved in the text field. So lets say it currently looks like this

{"amount":"45","level":1}

I want to update the amount value for every one of these rows, to for example "level" * 5.

I can't figure out a way to do this with one query since it does not seem possible to alter a single value for a text type field like this. Or am I missing something? Otherwise i will just have to alter it manually for every single row I need to change which would be a pain.

First see if you can cast the text to jsonb thusly: select mycol::jsonb from mytable — Mike Organek
– Mike Organek, Commented Apr 14, 2021 at 9:20

user330315 · Accepted Answer · 2021-04-14 09:23:00Z

You need to first cast the value to a proper jsonb value, then you can manipulate it using JSON functions.

update the_table
   set the_column = jsonb_set(the_column::jsonb, '{level}', to_jsonb((the_column::jsonb ->> 'level')::int * 5))::text
where ....

The expression (the_column::jsonb ->> 'level')::int * 5 extracts the current value of level converts it into an integer and multiplies it with 5. The to_jsonb() around it is necessary because jsonb_set() requires a jsonb value as the last parameter

The '{level}' parameter tells jsonb_set() to put the new value (see above) into the (top level) key level

And finally the whole jsonb value is converted back to a text value.

If you really store JSON in that column, you should think about converting that column to the jsonb data type to avoid all that casting back and forth.

Or maybe think about a properly normalized model where this would be as simple as set level = level * 5

Collectives™ on Stack Overflow

Alter single value in json string in text field

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related