In postgres select, return a subquery of a column as an array?

Question

(did it before, but memory disappears, like goggle)

want to get a selection from usersusing tag.tag_idfor 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 an idea inline query tag_arrwill 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

, , , , .