I have a table representing an object:
id|field1|field2|field3 --|------|------|------ 1 | b | f | z 2 | q | q | q
I want to pass several objects to pg function which will update corresponding rows.
Now I see only one way to do it – to pass a jsonb with array of objects. For example:
[{"id":1, "field1":"foo", "field2":"bar", "field3":"baz"},{"id":2, "field1":"fooz", "field2":"barz", "field3":"bazz"}]
Is this a best way to perform an update? And what is the best way to do it with jsonb input?
I don’t really like the way to convert jsonb input to rows with select * from json_each('{"a":"foo", "b":"bar"}')
and operating it. I would prefer some way to execute a single UPDATE
.
Advertisement
Answer
You can do it in next way (using json_populate_recordset):
update test set field1 = data.field1, field2 = data.field2, field3 = data.field3 from (select * from json_populate_recordset( NULL::test, '[{"id":1, "field1":"f.1.2", "field2":"f.2.2", "field3":"f.3.2"},{"id":2, "field1":"f.1.4", "field2":"f.2.5", "field3":"f.3.6"}]' )) data where test.id = data.id;