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

Question

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.

Answer 1

First cte unnest all elements of the array, second one update each element and then simply update the original table building the array again.

with ct as
(
    select id, jsonb_array_elements(data) dt
    from   t
)
, ct2 as
(
  select id, jsonb_set(dt, '{updatedAt}', '"1571150000"', false) dt2
  from   ct
)
update t
set    data = (select jsonb_agg(dt2) from ct2 where ct2.id = t.id);

select * from t;

id | data                                                                                                                                
-: | :-----------------------------------------------------------------------------------------------------------------------------------
 1 | [{"id": "1", "status": "test status1", "updatedAt": "1571150000"}, {"id": "2", "status": "test status2", "updatedAt": "1571150000"}]

db<>fiddle here

Answer 2

You declared Postgres 10, for which McNets provided a valid solution. It should be a bit more efficient without CTEs as those cannot be inlined before Postgres 12:

UPDATE tbl
SET    data = (
   SELECT jsonb_agg(jsonb_set(d, '{updatedAt}', '"15711500000"', false))
   FROM   jsonb_array_elements(data) d
   );

jsonb_set() is handy, but the operation is still pretty inefficient while not all (or most) rows actually need an update. All rows are updated - as can be seen in this extended test:

db<>fiddle here

If only a (small) part of all rows actually needs an update, it's much more efficient to update only those. Better yet, identify affected rows with index support to begin with. Not trivial in Postgres 10 (would require a tailored expression index). Much simpler and more efficient in Postgres 12 with the SQL/JSON path language:

UPDATE tbl
SET    data = (
   SELECT jsonb_agg(jsonb_set(dt, '{updatedAt}', '"15711500000"', false))
   FROM   jsonb_array_elements(data) dt
   )
WHERE  data @? '$.updatedAt ? (@ != "1571150000")';

db<>fiddle here

The added WHERE data @? '$.updatedAt ? (@ != "1571150000")' basically says:

'Look at the key "updatedAt" (in all array elements) at the top level and check whether any of them is not equal to "1571150000"; return true if (and only if) such a key is found.'

Identifies affected rows with index support. The manual:

Also, GIN index supports @@ and @? operators, which perform jsonpath matching.

!= is harder to support than ==. But index support can still make a huge difference.