Convert object array to array compatible for nodejs/pg/unnest

Question

In regards to UPDATE multiple rows from multiple params in nodejs/pg, I need to run the following:

update portfolios p
set votes = s.votes
from unnest(array[(5, 1), (15, 1), (25, 2)]) s (votes int, id int)
where p.id = s.id

where my array in unnest is $1, as follows:

update portfolios p
set votes = s.votes
from unnest($1) s (votes int, id int)
where p.id = s.id

However, my array originally consist of objects, as:

[{votes: 5, id: 1}, {votes: 15, id: 1}, {votes: 25, id: 2}]

I've tried to convert it with:

my_array = my_array.map(function(e) { return tuple(e.votes, e.id); });

But that fails.

I need to correct compatible array with values for use with pg and Client.query.

How can I convert my array of objects to respect javascript and postgresql unnest?

Answer 1

You could send your JSON string as is, and have PostgreSQL deal with it:

update portfolios p
set votes = s.votes
from (
  select (e->>'votes')::int as votes, (e->>'id')::int as id
  from (select (regexp_replace($1, '"\1"', 'g'))::jsonb as jarr) j
  cross join jsonb_array_elements(jarr) e
  ) s
where p.id = s.id;

Where $1 is [{votes: 5, id: 1}, {votes: 15, id: 1}, {votes: 25, id: 2}]', '([a-z]+) as a string.