Postgres -将JSON对象中的所有值更新为文本

Question

我有一个包含不同键的JSON列的表，其中的一些值是数字，但我希望所有的值都是文本。例如，我有{"budget": 500, "budget_id": 100, ...[dozens more keys]}，但我希望它是{"budget":"500", "budget_id": "100"}。我希望有一个查询能够盲目地返回一个JSON对象，该对象的值都是文本。与…有关的东西：

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

Answer 1

这就是了。首先，使用json_each_text (值作为文本)将JSON扁平化为一组键值对，然后使用json_object_agg将其聚合回JSON。

update my_table
set data = (select json_object_agg(key, value) from json_each_text(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;

Answer 2

我认为，基于Postgres - Update all values in a JSON object to be text中先前给出的有帮助的答案，我能够调整查询：

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;