'UPDATE deeply nested jsonb array structure in PostgreSQL

I have a simple table with a structure as follows:

+----+------------------------------------------------------------------------------------------+
| id | data (jsonb)                                                                             |
+----+------------------------------------------------------------------------------------------+
| 1  | {a: 1, b: [{id: 10, name: "Ton"}, {id: 20, name: "Maria"}, {id: 30, name: "Jack"}]       |
+----+------------------------------------------------------------------------------------------+
| 2  | {a: 2, b: [{id: 40, name: "Hello!"}, {id: 50, name: "Peter"}, {id: 60, name: "Wilson"}]} |
+----+------------------------------------------------------------------------------------------+
| 3  | {a: 3, b: [{id: 10, name: "Ton"}, {id: 70, name: "Joana"}, {id: 80, name: "Ana"}]}       |
+----+------------------------------------------------------------------------------------------+

If you look closely at rows 1 and 3, I have two listings inside the data.b array with the same id and name:

{id: 10, name: "Ton"}

How can I update both nested ocurrences of "Ton" (id: 10) with PostgreSQL?

For example, if "Ton" was a typo and now needs to be updated to "Tom", my query logic would be:

 1. Find all "data.b" occurrences where id = 10 (Ton)
 2. If any was found, update "data.b[index].name" to "Tom"

I managed to update a single occurrence by manually using jsonb_set() and passing the index of the occurrences, but how can I make work with an array of indefinite occurrences?

-- Works well updating a single occurrence in a single index.
-- How write this to update all occurrences in the jsonb array?
UPDATE my_table SET data = jsonb_set(data, '{b,0,name}', 'Tom') WHERE layout_news::json#>>'{b,0,id}' = '10'

Is it something possible to do with PostgreSQL?



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source