CODEWITHSUNDEEP
javascriptarraysnode.jspostgresql
Michael Nielsen
Michael Nielsen

Reputation: 1242

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

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?

Upvotes: 7

Views: 2076

Answers (2)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125244

@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);

Upvotes: 1

Ezequiel Tolnay
Ezequiel Tolnay

Reputation: 4582

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.

Upvotes: 1

Related Questions