in postgres select, return a column subquery as an array?

Question

(have done this before, but memory fades, as does goggle)

wish to get select from users with the tag.tag_ids for each user returned as an array.

 select usr_id,        name,        (select t.tag_id from tags t where t.usr_id = u.usr_id) as tag_arr from   users u; 

with the idea embedded query tag_arr would be an array

Answer 1

Use the aggregate function:

select     usr_id,      name,      array_agg(tag_id) as tag_arr from users join tags using(usr_id) group by usr_id, name 

or an array constructor from the results of a subquery:

select     u.usr_id,      name,      array(         select tag_id          from tags t          where t.usr_id = u.usr_id         ) as tag_arr from users u 

The second option is a simple one-source query while the first one is more generic, especially convenient when you need more than one aggregate from a related table. Also, the first variant should be faster on larger tables.

Note, that for better performance the usr_id columns in both tables should be indexed. While typically users.usr_id is a primary key, sometimes one may forget that the index of referencing column is also useful.