Join Postgresql array to table

Question

I have following tables

create table top100
(
    id  integer   not null,    
    top100ids integer[] not null
);

create table top100_data
(
    id  integer   not null,    
    data_string text not null
);

Rows in table top100 look like:
1, {1,2,3,4,5,6...100}
Rows in table top100_data look like:
1, 'string of text, up to 500 chars'

I need to get the text values from table top100_data and join them with table top100.
So the result will be:
1, {'text1','text2','text3',...'text100'}

I am currenly doing this on application side by selecting from top100, then iterating over all array items and then selecting from top100_data and iterating again + transforming ids to their _data text values. This can be very slow on large data sets.

Is is possible to get this same result with single SQL query?

Gordon Linoff · Accepted Answer · 2019-05-19 21:52:26Z

3

You can unnest() and re-aggregate:

select t100.id, array_agg(t100d.data order by top100id)
from top100 t100 cross join
     unnest(top100ids) as top100id join
     top100_data t100d
     on t100d.id = top100id
group by t100.id;

Or if you want to keep the original ordering:

select t100.id, array_agg(t100d.data order by top100id.n)
from top100 t100 cross join
     unnest(top100ids) with ordinality as top100id(id, n) join
     top100_data t100d
     on t100d.id = top100id.id
group by t100.id;

answered May 19, 2019 at 21:52

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Comments

Shawn.X · Accepted Answer · 2019-05-20 06:35:33Z

Just use unnest and array_agg function in PostgreSQL, your final sql could be like below:

with core as (
select
    id,
    unnest(top100ids) as top_id
from
    top100
)
select
    t1.id,
    array_agg(t1.data_string) as text_datas
from
    top100 t1
join
    core c on t1.id = c.top_id

The example of unnest as below:

postgres=# select * from my_test;
 id |   top_ids    
----+--------------
  1 | {1,2,3,4,5}
  2 | {6,7,8,9,10}
(2 rows)

postgres=# select id, unnest(top_ids) from my_test;
 id | unnest 
----+--------
  1 |      1
  1 |      2
  1 |      3
  1 |      4
  1 |      5
  2 |      6
  2 |      7
  2 |      8
  2 |      9
  2 |     10
(10 rows)

The example of array_agg as below:

postgres=# select * from my_test_1 ;
 id | content 
----+---------
  1 | a
  1 | b
  1 | c
  1 | d
  2 | x
  2 | y
(6 rows)

postgres=# select id,array_agg(content) from my_test_1 group by id;
 id | array_agg 
----+-----------
  1 | {a,b,c,d}
  2 | {x,y}
(2 rows)

Collectives™ on Stack Overflow

Join Postgresql array to table

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related