2 Answers
2

1 general way to do this would be to explode the json, replace values using plain old sql & aggregate back to the original json shape. But this requires you to have full knowledge of the document structure

When a FROM clause is present, what essentially happens is that the
target table is joined to the tables mentioned in the from_list, and
each output row of the join represents an update operation for the
target table. When using FROM you should ensure that the join produces
at most one output row for each row to be modified. In other words, a
target row shouldn't join to more than one row from the other
table(s). If it does, then only one of the join rows will be used to
update the target row, but which one will be used is not readily
predictable.

What you can do is unnest your jsons with functions-json and then aggregate them back: