Question 115013 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Postgres – Update all values in a JSON object to be text

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()];
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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;

  2. 0

    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;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search