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...

klin · Accepted Answer · 2015-12-15 20:59:39Z

0

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;

answered Dec 15, 2015 at 20:59

klin

123k15 gold badges241 silver badges263 bronze badges

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related