PostgreSQL : Update multiple inner jsonb objects fields with same name

Question

I have a table named test which has 2 columns : (id int, md jsonb). md column can contain data like this

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

now I want to update all instances of alice to bob.

I got the ids of rows containing alice by doing

select id from test, lateral jsonb_each(md) where md->>'author' = 'alice';

Are there any Postgres facilities to update every inner object which contains the author field?

Any suggestions is appreciated.

Answer 1

I agree with @a_horse_with_no_name that it's better to review your storage. But it's interesting to do as an execrcise. I think the only way to do this is by expanding json with jsonb_each, updating data with jsonb_set and then aggregating it back with 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