Bulk update in PostgreSQL by unnesting a JSON array

Question

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.

There is no table (definition) in your question. Only something that looks like json. and some text. — wildplasser
– wildplasser, Commented Jan 21, 2022 at 13:16

klin · Accepted Answer · 2022-01-21 13:01:27Z

3

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.

answered Jan 21, 2022 at 13:01

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user330315 · Accepted Answer · 2022-01-21 12:47:05Z

2

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

answered Jan 21, 2022 at 12:47

user330315

Collectives™ on Stack Overflow

Bulk update in PostgreSQL by unnesting a JSON array

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related