Postgres - Update all values in a JSON object to be text-CodePudding

I have a table with a JSON column that is full of different keys and some of the values in it are numbers but I want all the values to be text. For example, I have {"budget": 500, "budget_id": 100, ...[dozens more keys]}, but I want it to be {"budget":"500", "budget_id": "100"}. I am hoping there is a single query that will blindly spit back a JSON object whose values are all text. Something along the lines of:

UPDATE my_table
SET data = data || --python-sql psuedo-code -> [key:quote_literal(value) for key in data.keys()];

CodePudding user response：

Here it is. First flatten the JSON into a set of key-value pairs using json_each_text (value as text) and aggregate back into JSON using json_object_agg.

update my_table
set data = (select json_object_agg(key, value) from json_each_text(data));

This approach can be used for other transformations too.
It might be a better idea to make a view instead of modifying the original data:

create view my_table_v as 
 select <the other fields>, 
        (select json_object_agg(key, value) from json_each_text(data)) as data 
 from my_table;

CodePudding user response：

i think based off of the helpful earlier answer given in Postgres - Update all values in a JSON object to be text I was able to adjust the query:

update my_table orig
set data = text_table.text_data from (
    select same_as_orig.id, json_object_agg(k, v) text_data          
    from my_table same_as_orig, json_each_text(data::json) _(k, v)
    group by same_as_orig.id
    ) text_table
where orig.id = text_table.id;