Convert an array of objects to an array compatible for nodejs / pg / unnest

Question

As for UPDATE a few lines from several parameters 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 unsest 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 initially consists of objects, for example:

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

I tried converting it with:

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

But it fails.

I need to fix a compatible array with values ​​for use with pg and Client.query.

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

Answer 1

You can send your JSON string as is, and it has PostgreSQL:

 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}]', '([az]+) as a string.

Answer 2

The @Ziggy idea of ​​passing JSON might work, although it would be ideal to force the driver to adapt your array. This is the final request that the driver must pass to Postgresql

 update portfolios p set votes = s.votes from ( select (a->>'votes')::int as votes, (a->>'id')::int as id from ( select jsonb_array_elements(a) as a from (values ('[{"votes": 5, "id": 1}, {"votes": 15, "id": 1}]'::jsonb)) s(a) ) s ) s where p.id = s.id 

And the request to transfer to the driver:

 update portfolios p set votes = s.votes from ( select (a->>'votes')::int as votes, (a->>'id')::int as id from ( select jsonb_array_elements(a) as a from (values (($1)::jsonb)) s(a) ) s ) s where p.id = s.id 

The $1 parameter should be jsonified with something like JSON.stringify :

 var a = JSON.stringify(my_array_of_objects);