How to get corresponding names for each element of an Postgresql array

Question

I have two tables tablea and tableb. Their sturcture is:

tablea
  id   serial
  name varchar
  alg  integer[]

tableb
  id          serial
  description varchar

in tablea.alg field I have multiple numbers of substances like {1,17,55,97} and in the query I want to get names instead of numbers like:

name      | substances
organism1 | substance 1, substance 17, substance 55, substance 97

Can any1 suggest the right query?

Answer to simmilar question is on StackOwerflow question, but how to use tables instead of fixed array values?

Thank you...

Answer 1

Use unnest() to get algs in separate rows, join tableb on the values and aggregate description with string_agg():

select name, string_agg(description, ', ') substances
from (
    select name, unnest(alg) alg
    from tablea
    ) a
join tableb on alg = id
group by name;