CODEWITHSUNDEEP
sqljsonpostgresql
yeaitsme
yeaitsme

Reputation: 91

Alter single value in json string in text field

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.

Upvotes: 0

Views: 108

Answers (1)

user330315
user330315

Reputation:

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

Upvotes: 3

Related Questions