title: PostgreSQL - How to return array of objects with nested array using joins
body: <p>I know basic PostgreSQL usage, but I'm looking to return data formatted in a specific way. The current query below lists all team names with their division name. I basically need to list all teams UNDER their respective division. I believe I need some sort of SELECT DISTINCT call but I am absolutely lost on what to correct terms to search for. Can anyone point me in the right direction? Is it best to do it in the Postgres query or should it be handled on the server (after the current response is returned)?</p>
  
  <p>Minimal examples below, Teams and Divisions tables are basically set up as id and name</p>
  
  <p><strong>Pivot table</strong></p>
  
  <pre><code>id | team_id | season_id | division_id
  --------------------------------------
  1  |    3    |     1     |      3   
  2  |    4    |     1     |      3   
  3  |    5    |     1     |      3   
  4  |    6    |     1     |      3   
  </code></pre>
  
  <p><strong>Query to list all teams and their division name</strong></p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT t.name AS team_name, d.name AS division_name FROM team_season_division tsd
  JOIN teams t ON t.id = tsd.team_id
  JOIN divisions d ON d.id = tsd.division_id
  WHERE tsd.season_id = 1;
  </code></pre>
  
  <p><strong>Current response</strong></p>
  
  <pre class="lang-js prettyprint-override"><code>[
      {
          team_name: 'synthesize onlines',
          division_name: 'A1',
      },
      {
          team_name: 'array arrays',
          division_name: 'A1',
      },
      {
          team_name: 'quantify matrixs',
          division_name: 'B1',
      }
  ]
  </code></pre>
  
  <p><strong>Example response desired</strong></p>
  
  <pre class="lang-js prettyprint-override"><code>[
      {
          division_name: 'A1',
          teams_in_division: [ 
              { name: 'synthesize onlines' }, 
              { name: 'array arrays' },
              { name: 'mobile microchips' } 
          ]
      },
      {
          division_name: 'B1',
          teams_in_divisions: [ 
              { name: 'quantify matrixs' }, 
              { name: 'matrix matrixs' }, 
              { name: 'hack hacks' }, 
              { name: 'bluetooth generates' }, 
              { name: 'override protocols' } 
          ]
      }
  ]
  </code></pre>