1

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.

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

0

You declared Postgres 10, for which McNets has 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
   );

However, still pretty inefficient while not all (or most) rows actually need an update. All rows are updated - as demonstrated in this fiddle with extended test case:

db<>fiddle here

If only a (small) part of all rows actually needs an update, it's much more efficient to update only those. Or, better yet, identify those rows with index support a priori. The first part is tedious and the second part not trivial in Postgres 10 (would require a specialized expression index).

Both has become 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")';

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

Most importantly, this eliminates non-qualifying rows from the UPDATE early. See:

db<>fiddle here

It also can use indexes. The manual:

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

But index support is limited in this particular case because != is much harder to support than ==.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.