Postgres: aggregate column into array
<p>I have the following two tables:</p>

<pre><code># select * from list;
  list_id |         name
 ---------+----------------------
        9 | Popular
       11 | Recommended
</code></pre>

<p>and</p>

<pre><code># select * from list_item;
 list_id | game_id | position 
---------+---------+----------
      11 |       2 |        0
       9 |      10 |        1
      11 |       5 |        1
      11 |       4 |        4
      11 |       6 |        2
      11 |       7 |        3
       9 |       3 |        0
</code></pre>

<p>I want an array of game IDs per list like so:</p>

<pre><code> list_id |     name    | game_ids
---------+-------------+------------
       9 | Popular     | {3,10}
      11 | Recommended | {2,5,6,7,4}
</code></pre>

<p>I came up with the following solution but it seems rather complicated especially the bit where I get the completed array using <code>distinct on</code> and  <code>last_value</code>:</p>

<pre class="lang-sql prettyprint-override"><code>with w as (
  select
    list_id,
    name,
    array_agg(game_id) over (partition by list_id order by position)
  from list
  join list_item
  using (list_id)
)
select
  distinct on (list_id)
  list_id,
  name,
  last_value(array_agg) over (partition by list_id)
from w
</code></pre>

<p>Any suggestions how to simplify this?</p>