sql - PostgreSQL JOIN with array type with array elements -

sql - PostgreSQL JOIN with array type with array elements -

i have 2 tables tags , users

table name: tags

| id | name | | 1 | 1 | | 2 | 2 | | 3 | 3 | | 4 | 4 | | 5 | 5 |

table name: users

| id | fname | tags | | 1 | ram | {1,5} | | 2 | sham | {1,2,3,4} | | 3 | bham | {1,3} | | 4 | kam | {5,2} | | 5 | lam | {4,2} |

expected output:

| id | fname | tags | | 1 | ram | one, 5 | | 2 | sham | one, two, three, 4 | | 3 | bham | one, 3 | | 4 | kam | five, 2 | | 5 | lam | four, 2 |

trial-1 : using join

select i.id, i.fname, i.tags, j.name users bring together tags j on j.id = any(i.cached_tag_ids) limit 1

result:

| id | fname | tags | | 1 | ram | 1 | | 1 | ram | 5 |

expected:

| id | fname | tags | | 1 | ram | one, 5 |

your tags should have integer[] type.

create table users( id serial, fname varchar(50), tags integer[] );

then,

select i.id, i.fname, array_agg(j.name) users left bring together tags j on j.id = any(i.tags) grouping fname,i.id order id

should work. see sqlfiddle

this question may help.

sql arrays postgresql join