CODEWITHSUNDEEP
sqlnode.jspostgresql
mclenithan
mclenithan

Reputation: 275

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

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!

Upvotes: 0

Views: 515

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656361

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

Upvotes: 1

null
null

Reputation: 3517

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

Upvotes: 0

Related Questions