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
– Carl Tashian, Commented May 5, 2016 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
– Michael Nielsen, Commented May 5, 2016 at 20:18
How does it fail? Is there a way to output the query that is being fed to Postgresql? — Clodoaldo Neto
– Clodoaldo Neto, Commented May 6, 2016 at 0: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
– Clodoaldo Neto, Commented May 6, 2016 at 0:56
I'm getting error 42804 "enforce_generic_type_consistency" from PostgreSQL if I keep my [{...}, {...}...] format. — Michael Nielsen
– Michael Nielsen, Commented May 6, 2016 at 5:27

Ezequiel Tolnay · Accepted Answer · 2016-05-06 03:30:24Z

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.

answered May 6, 2016 at 3:30

Ezequiel Tolnay

4,5821 gold badge21 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 10:28:08Z

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

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related