Postgres: aggregate column into array

Question

I have the following two tables:

# select * from list;
  list_id |         name
 ---------+----------------------
        9 | Popular
       11 | Recommended

and

# 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

I want an array of game IDs per list like so:

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

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

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

Any suggestions how to simplify this?

If your problem in order by then you can specify it in the aggregate for some aggregate functions, so: select list_id, name, array_agg(game_id order by position) from list join list_item using (list_id) group by list_id, name; should be enough. — Abelisto
– Abelisto, Commented Aug 8, 2016 at 20:09
@AlexanderGuz No, it is not a requirement to use window. Sorry, should have made this clearer. — peterwimsey
– peterwimsey, Commented Aug 9, 2016 at 10:03
Post that as an answer, and eventually accept it, so it the question does not stay answered. — Clodoaldo Neto
– Clodoaldo Neto, Commented Aug 9, 2016 at 10:52

peterwimsey · Accepted Answer · 2016-08-09 11:11:11Z

16

Here is a better solution as suggested by Abelisto in the comments:

select
  list_id,
  name,
  array_agg(game_id order by position)
from list
join list_item
using (list_id)
group by list_id, name

answered Aug 9, 2016 at 11:11

peterwimsey

5622 gold badges6 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres: aggregate column into array

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related