这非常丑陋,并显示了一个用例,其中使用JSON对模型进行规范化是一个错误的选择(如果那是正确的一对多关系,则列font_size
将被定义为整数) ,那么您就不会遇到这个问题了。
我唯一能想到的就是取消所有数组元素的嵌套,将值更改为整数,将“小部件”聚合回数组,并使用它来更新表。
以下代码假定您的表中有一个名为id
的主键列。
第一步是将fontSize值替换为适当的整数。
select t.id,jsonb_set(w.jw,'{fontSize}',to_jsonb((w.jw ->> 'fontSize')::int))
from the_table t
cross join jsonb_array_elements(t.the_value -> 'resources' -> 'widgets') as w(jw)
jsonb_array_elements()
将每个小部件作为单个JSONB值(在单独的行中)返回。 to_jsonb((w.jw ->> 'fontSize')::int
将键fontSize
的当前值转换为整数,jsonb_set()
将其放回JSON值。
给出返回的示例数据(包括假定的主键列)
id | jsonb_set
---+------------------------------------------------------------
42 | {"id": 1,"color": "#ffffff","width": 150,"fontSize": 5}
42 | {"id": 2,"color": "#aaaaaa","width": 200,"fontSize": 10}
现在可以将其聚合回一个数组:
select t.id,jsonb_agg(jsonb_set(w.jw,to_jsonb((w.jw ->> 'fontSize')::int))) as new_widgets
from the_table t
cross join jsonb_array_elements(t.the_value -> 'resources' -> 'widgets') as w(jw)
group by id
鉴于上述示例日期,现在返回:
id | new_widgets
---+---------------------------------------------------------------------------------------------------------------------------
42 | [{"id": 1,"fontSize": 5},{"id": 2,"fontSize": 10}]
此查询现在可以用作UPDATE语句的源,该语句再次使用jsonb_set()
来更改表中的值。
update the_table
set the_value = jsonb_set(the_value,'{resources,widgets}',x.new_widgets)
from (
select t.id,to_jsonb((w.jw ->> 'fontSize')::int))) as new_widgets
from the_table t
cross join jsonb_array_elements(t.the_value -> 'resources' -> 'widgets') as w(jw)
group by id
) as x
where x.id = the_table.id;
完整的在线示例:https://rextester.com/OIFOBX92774
如果您的列未定义为jsonb
(应为该列),则需要在上述查询中将其强制转换为the_column::jsonb
。
本文链接:https://www.f2er.com/3139410.html