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?

Why are you needing to make this single `update` statement happen; why not run multiple `update` statements? — Carl Tashian, May 05 '16 at 20:02
I'm using nodejs with pg which doesn't support multiple queries without making a manual loop between all statements - apparently. If other solution is available I'm all ears. — Michael Nielsen, May 05 '16 at 20:18
How does it fail? Is there a way to output the query that is being fed to Postgresql? — Clodoaldo Neto, May 06 '16 at 00:55
If it is not possible to get the output from the driver it is possible to see it in the Postgresql log. — Clodoaldo Neto, May 06 '16 at 00:56
I'm getting error 42804 "enforce_generic_type_consistency" from PostgreSQL if I keep my [{...}, {...}...] format. — Michael Nielsen, May 06 '16 at 05:27
Yes, if you wanted to use multiple queries you would have to loop through all the values and kick off one `update` statement for each value. But, can you explain why that isn't a reasonable solution? — Carl Tashian, May 06 '16 at 20:40
@CarlTashian: That is reasonable but not optimal mainly because each update will be a round trip to the server. — Clodoaldo Neto, May 09 '16 at 14:39

score 1 · Answer 1 · answered May 06 '16 at 03:30

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.

score 1 · Answer 2 · edited May 23 '17 at 10:28

@Ziggy idea to pass JSON can work although the ideal would be to make the driver adapt your array. This is the final query 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 query to pass 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 must be jsonified with something like JSON.stringify:

var a = JSON.stringify(my_array_of_objects);

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

2 Answers2

Linked