How to properly loop through a table and output to an array in PostgreSQL?

Question

Here is a DB example

table "Users" fname | lname | id | email Joe | smith | 1 | [email protected] Bob | smith | 2 | [email protected] Jane | smith | 3 | [email protected] table "Awards" userId | award 1 | bigaward 1 | smallaward 1 | thisaward 2 | thataward table "Invites" userId | invited 1 | true 3 | true

Basically, how do you write a query in PostgreSQL that allows you to create something like this:

[{ fname:"Joe", lname:"Smith", id: 1, email: "[email protected]", invited: true, awards: ["bigaward", "smallaward", "thisaward"] }, { fname:"Jane", lname:"Smith", id: 3, email: "[email protected]", invited: true, awards: [] }]

Here is what I am trying to do...

SELECT users.fname, users.lname, users.id, users.email, invites.invited, awards.award(needs to be an array) FROM users JOIN awards on ....(unknown) JOIN invites on invites.userid = users.id WHERE invited = true

the above array would be the desired output, just can't figure out a good one shot query. I tried the PostgreSQL docs, but to no avail. I think I might need a WITH statement?

Thanks in advance, Postgres guru!

PostgreSQL v. 9.2

Answered by RhodiumToad on the postgresql IRC:

SELECT users.fname, users.lname, .... array(select awards.award from awards where a.id = user.id) as awards FROM users JOIN invites on invites.userid = users.id WHERE invited = true

array() then through a query inside of it... brilliant!

possible duplicate of How to concatenate strings of a string field in a PostgreSQL 'group by' query? — Stobor
– Stobor, Commented Jul 22, 2013 at 23:06
I don't think it's a duplicate of that question, because the final output is re-normalized, although it would work for querying the data in a relatively simple manner. — user2246674
– user2246674, Commented Jul 22, 2013 at 23:08
Please clarify: do you want valid JSON fomat? Also, as always, your version of Postgres. — Erwin Brandstetter
– Erwin Brandstetter, Commented Jul 22, 2013 at 23:09
Erwin, it's updated, it's a raw query through sequelize on node.js, it always comes out JSON. Thanks! — mclenithan
– mclenithan, Commented Jul 22, 2013 at 23:13

Erwin Brandstetter · Accepted Answer · 2013-07-22 23:37:42Z

I think it can be as simple as:

SELECT u.fname, u.lname, u.id, u.email, i.invited, array_agg(a.award) FROM users u JOIN invites i ON i.userid = u.id AND i.invited LEFT JOIN awards a ON a.userid = u.id GROUP BY u.fname, u.lname, u.id, u.email, i.invited

Your display in JSON format just makes it seem more complicated.

Use a basic GROUP BY including all columns not to be aggregated - leaving award, which you aggre3gate into an array with the help of the aggregate function array_agg().

The LEFT JOIN is important, so not to exclude users without any awards by mistake.

Note that I ignored CaMeL-case spelling in my example to avoid the ugly double-quoting.

This is considerably faster for bigger tables than notoriously slow correlated subqueries - like demonstrated in the added example in the question.

->SQLfiddle demo

null · Accepted Answer · 2013-07-22 23:07:30Z

You will need to use a hasNext or forEach (or similar) method to iterate trough the database. Whilst you're iterating you can add the results to an array and then encode that array to JSON.

I think I may have misunderstood your question. Apologies if inhave

Collectives™ on Stack Overflow

How to properly loop through a table and output to an array in PostgreSQL?

2 Answers 2

Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Linked

Related