时间戳

首页 > 解决方案 > 更新 PostgreSQL 中的 jsonb 列表项

sql - 更新 PostgreSQL 中的 jsonb 列表项

问题描述

我正在尝试更新jsonb列表中 1 个字段的键名。

初始数据

select jsonb_array_elements(status_history) from deals;
>>> [{"date": "2020-10-02T12:05:02.728691", "state": "gathering_info"}, {"date": "2020-10-08T10:15:20.798500", "state": "archived"}, {"date": "2020-10-08T10:43:17.651033", "state": "gathering_info"}]
>>> ...

我可以使用此功能检索每个元素

select jsonb_array_elements(status_history) from deals;

>>> {"date": "2020-10-02T11:51:55.624263", "state": "gathering_info"}

我想更新state密钥status，我尝试这样做

update deals set elem=elem & {'status': elem->'state'} from (select jsonb_array_elements(status_history) from deals) elem;
update deals set elem->'status' = elem->'state' from (select jsonb_array_elements(status_history) from deals) elem;

但这是行不通的。我怎么能做到这一点？

标签： sqlarraysjsonpostgresqlpostgresql-9.5

解决方案

您可以在跟踪每个对象的位置的同时取消嵌套数组，使用运算符-和更改每个单独元素中的键名||，然后重新聚合。假设您的表的主键是id，您可以将其表述为：

update deals d
set status_history = d1.status_history
from (
    select d.id, 
        json_agg(
            x.obj - 'state' || jsonb_build_object('status', x.obj -> 'state')
            order by x.n
        ) as status_history
    )
    from deals d
    cross join lateral jsonb_array_elements(d.status_history) with ordinality as x(obj, n)
    group by d.id
) d1
where d1.id = d.id

推荐阅读