I have a table representing an object:
x
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;