postgresql - PostgreSQL:使用相同名称更新多个内部jsonb对象字段

我有一个名为test的表，它有两列：(id int, md jsonb)。md列可以包含这样的数据

{
  "a": {
    ...
    "author": "alice"
    ...
  },
  "b": {
    ...
    "author": "alice"
    ...
  }
}

现在我想将alice的所有实例更新为bob。
我得到了包含alice的行的id
select id from test, lateral jsonb_each(md) where md->>'author' = 'alice';
是否有Postgres工具来更新包含author字段的每个内部对象？
如有任何建议，我们将不胜感激。

最佳答案

我同意“一匹没有名字的马”的说法，那就是最好检查一下你的储藏室。但作为一名执行官很有意思。我认为唯一的方法是用jsonb_each扩展json，用jsonb_set更新数据，然后用jsonb_object_agg聚合回来：

update test as t set
    md = (
    select
        jsonb_object_agg(
            d.key,
            case
                when d.value->>'author' = 'alice' then
                    jsonb_set(d.value, '{author}', '"bob"')
                else
                    d.value
            end
        )
     from lateral jsonb_each(t.md) as d
    )
where
    exists (select * from jsonb_each(t.md) as d where d.value->>'author' = 'alice')

db<>fiddle demo