Bulk update in PostgreSQL by unnesting a JSON array-CodePudding

I want to do a batch update in PostgreSQL in one go by passing in an array of JSON objects, but I am not sure how I should approach the problem.

An example:

[
  { "oldId": 25, "newId": 30 },
  { "oldId": 41, "newId": 53 }
]

should resolve as: UPDATE table SET id = 30 WHERE id = 25 and UPDATE table SET id = 41 WHERE id = 53, in a single command, of course.

CodePudding user response：

Use the function jsonb_array_elements() in the from clause:

update my_table
set id = (elem->'newId')::int
from jsonb_array_elements(
    '[
      { "oldId": 25, "newId": 30 },
      { "oldId": 41, "newId": 50 }
    ]') as elem
where id = (elem->'oldId')::int

Note that if the column id is unique (primary key) the update may result in a duplication error depending on the data provided.

Db<>fiddle.

CodePudding user response：

You need to unnest the array the cast the elements to the proper data type:

update the_table
   set id = (x.item ->> 'newId')::int
from jsonb_array_elements('[...]') x(item)
where the_table.id = (x.item ->> 'oldId')::int