Sample Header Ad - 728x90

Update all values for given key nested in JSON array of objects

8 votes
2 answers
13291 views
I have a jsonb column data in a Postgres table my_table. It contains the following data:
[
   {"id":"1","status":"test status1","updatedAt":"1571145003"},
   {"id":"2","status":"test status2","updatedAt":"1571145323"}
]
I want to update the updatedAt key of all objects in that array using one query. I tried: update my_table set data = data || '{"updatedAt": "1571150000"}'; The above query added a new object within the array like the following:
[
   {"id":"1","status":"test status1","updatedAt":"1571145003"},
   {"id":"2","status":"test status2","updatedAt":"1571145323"},
   {"updatedAt":"1571150000"}
]
I want the output like:
[
   {"id":"1","status":"test status1","updatedAt":"1571150000"},
   {"id":"2","status":"test status2","updatedAt":"1571150000"}
]
I also tried jsonb_set(), but that needs the second parameter to be the array index. I can't be sure of the count of JSON objects in the array. If this can be solved with custom functions, also fine.
Asked by kishram (83 rep)
Oct 15, 2019, 01:35 PM
Last activity: Dec 28, 2023, 05:51 AM