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 slightly 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 inefficient while some rows don't actually need an update. This query writes a new version for very row. See:

fiddle

It's (much) more efficient to only update rows that actually change. Better yet, identify those with index support to begin with. Not trivial in Postgres 10 (would require a tailored expression index). Much simpler and more efficient since 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")';

fiddle

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 qualifying 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.