title: Group by column to get array results in Postgresql
body: <p>I have a table called <code>moviegenre</code> which looks like:</p>
  
  <pre><code>moviegenre:
  - movie (FK movie.id)
  - genre (FK genre.id)
  </code></pre>
  
  <p>I have a query (ORM generated) which returns all <code>movie.imdb</code> and <code>genre.id</code>'s which have <code>genre.id</code>'s in common with a given <code>movie.imdb_id</code>.</p>
  
  <pre><code>SELECT "movie"."imdb_id", 
         "moviegenre"."genre_id" 
  FROM   "moviegenre" 
         INNER JOIN "movie" 
                 ON ( "moviegenre"."movie_id" = "movie"."id" ) 
  WHERE  ( "movie"."imdb_id" IN (SELECT U0."imdb_id" 
                                 FROM   "movie" U0 
                                        INNER JOIN "moviegenre" U1 
                                                ON ( U0."id" = U1."movie_id" ) 
                                 WHERE  ( U0."last_ingested_on" IS NOT NULL 
                                          AND NOT ( U0."imdb_id" IN 
                                                    ( 'tt0169547' ) ) 
                                          AND NOT ( U0."imdb_id" IN 
                                                    ( 'tt0169547' ) ) 
                                          AND U1."genre_id" IN ( 2, 10 ) )) 
           AND "moviegenre"."genre_id" IN ( 2, 10 ) ) 
  </code></pre>
  
  <p>The problem is that I'll get results in the format:</p>
  
  <pre><code>[
    ('imdbid22`, 'genreid1'),
    ('imdbid22`, 'genreid2'),
    ('imdbid44`, 'genreid1'),
    ('imdbid55`, 'genreid8'),
  ]
  </code></pre>
  
  <p>Is there a way within the query itself I can group all of the genre ids into a list under the <code>movie.imdb_id</code>'s? I'd like do to grouping in the query.<br>
  Currently doing it in my web app code (Python) which is extremely slow when 50k+ rows are returned.</p>
  
  <pre><code>[
    ('imdbid22`, ['genreid1', 'genreid2']),
    ('imdbid44`, 'genreid1'),
    ('imdbid55`, 'genreid8'),
  ]
  </code></pre>
  
  <p>thanks in advance!</p>
  
  <p>edit: </p>
  
  <p>here's the python code which runs against the current results</p>
  
  <p>results_list = []</p>
  
  <pre><code>for item in movies_and_genres:
      genres_in_common = len(set([
          i['genre__id'] for i in movies_and_genres
          if i['movie__imdb_id'] == item['movie__imdb_id']
      ]))
      imdb_id = item['movie__imdb_id']
  
      if genres_in_common &gt;= min_in_comon:
          result_item = {
              'movie.imdb_id': imdb_id,
              'count': genres_in_common
          }
          if result_item not in results_list:
              results_list.append(result_item)
  
  return results_list
  </code></pre>