Postgres Update Number Values in a JSONB field to be text

user_19804621 0
Viewed 24

I have a table with a JSON column and some of the values in it are numbers but I want all the values to be text. For example, I have {"budget": 500}, but I want it to be {"budget":"500"}. I have tried using the JSONB_SET function but even after postgres returns N rows updated, when I go to retrieve the records, they are still numbers. I was hoping that somebody may have encountered this issue. Here's what I've tried that isn't working.

UPDATE my_table
SET data = JSONB_SET(data, '{budget}', data->'budget'::text)
WHERE data ? 'budget' = true;

Since this is a very large table, hardcoding values is not feasible. If anybody knows why this isn't working or if there is something that does work, please let me know, thank you!

postgresql
1 Answers

You can enforce converting a JSONB number to text with the function quote_ident():

UPDATE my_table
SET data = jsonb_set(data, '{budget}', quote_ident(data->>'budget')::jsonb)
WHERE data ? 'budget'
-- you can add this condition to avoid updating non-numbers
-- AND jsonb_typeof(data->'budget') = 'number'

Note that data->'budget'::text does nothing as the cast refers to 'budget', not a JSON object and the expression is equivalent to data->'budget'.

user_19957380
Related