CODEWITHSUNDEEP
arrayspostgresqlsubqueryaggregate-functions
cc young
cc young

Reputation: 20203

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

(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

Upvotes: 61

Views: 60641

Answers (2)

Ali Sajjad Rizavi
Ali Sajjad Rizavi

Reputation: 4470

Use the array constructor of PostgreSQL:

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

Note:

If you're using psycopg2 with python, then the result will be converted to a python list as well! (Although for uuid[] array, you will need to convert it to text[] array using array(...)::text[], if you want to get IDs in python list). See this for details.

Upvotes: 8

klin
klin

Reputation: 121524

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.

Upvotes: 97

Related Questions