get JOIN table as array of results with PostgreSQL/NodeJS
<p>I'm creating an app where users are able to create questions, and others can upvote/downvote them.</p>

<p>The following is a part of my sql schema:</p>

<pre><code>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
);
</code></pre>

<p>What I would like to have is Postgres giving me each question with an array of votes, like that:</p>

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

<p>I looked at aggregate functions (like array_agg()) but it gave me only the values. A JOIN gave me a question joined with a vote, and would force me to do server side operations, which I would prefer not to.</p>

<p>Is there any way to do that? Is my reasoning regarding what I want to obtain wrong?</p>

<p>Thanks for your time.</p>