Get a JOIN table as an array of results using PostgreSQL / NodeJS

Question

I am creating an application in which users can create questions and others can move / lower them.

Below is my sql schema:

CREATE TABLE "questions" ( id SERIAL, content VARCHAR(511) NOT NULL, created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(), CONSTRAINT pk_question PRIMARY KEY (id) ); CREATE TABLE "votes" ( id SERIAL, value INT, question_id INT NOT NULL, CONSTRAINT pk_vote PRIMARY KEY (id), CONSTRAINT fk_question_votes FOREIGN KEY (question_id) REFERENCES questions (id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE ); 

What I would like is Postgres giving me every question with an array of votes, for example:

 [{ // a question id: 1, content: 'huh?', votes: [{ // a vote id: 1, value: 1 }, { // another vote id: 2, value: -1 }] }, { /*another question with votes*/ }] 

I looked at aggregate functions (e.g. array_agg ()), but it gave me only the values. JOIN asked me a question related to voting and would force me to perform server-side operations, which I would prefer not to do.

Is there any way to do this? Can I reason about what I want to get wrong?

Thank you for your time.

Answer 1

This is easy to do with pg-promise :

 function buildTree(t) { return t.map('SELECT * FROM questions', [], q => { return t.any('SELECT id, value FROM votes WHERE question_id = $1', q.id) .then(votes => { q.votes = votes; return q; }); }).then(t.batch); // settles the array of promises generated } db.task(buildTree) .then(data => { console.log(data); // your data tree }) .catch(error => { console.log(error); }); 

API: map , any , task , batch

---

Related questions:

• Get parent tree + tree with pg promises
• Conditional task with pg-promises

---

And if you want to use only one query, then using the syntax of PostgreSQL 9.4 and later, you can do the following:

 SELECT json_build_object('id', q.id, 'content', q.content, 'votes', (SELECT json_agg(json_build_object('id', v.id, 'value', v.value)) FROM votes v WHERE q.id = v.question_id)) FROM questions q 

And then your pg-promise example:

 var query = `SELECT json_build_object('id', q.id, 'content', q.content, 'votes', (SELECT json_agg(json_build_object('id', v.id, 'value', v.value)) FROM votes v WHERE q.id = v.question_id)) json FROM questions q`; db.map(query, [], a => a.json) .then(data => { console.log(data); // your data tree }) .catch(error => { console.log(error); }); 

Conclusion

The choice between the two approaches above should be based on the performance requirements of your application:

• A single-query approach is faster, but difficult to read or extend, being rather verbose
• A multi-query approach is easier to understand and extend, but it is not very good for performance due to the dynamic number of queries executed.