'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 |
|---|
