PostgreSQL JOIN with array type with array elements

Question

I have two tables tags and users

Table Name: tags

| id | name  |  
| 1  | one   |   
| 2  | two   |   
| 3  | three |   
| 4  | four  |   
| 5  | five  |   

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, five             |
| 2  | Sham  | one, two, three, four |
| 3  | Bham  | one, three            |
| 4  | Kam   | five, two             |
| 5  | Lam   | four, two             |

Trial-1 : using JOIN

SELECT I.id, I.fname, I.tags, J.name FROM users I 
JOIN tags J ON J.id = ANY(I.cached_tag_ids) 
LIMIT 1

Result:

| id | fname | tags |
| 1  | Ram   | one  |
| 1  | Ram   | five |

Expected:

| id | fname | tags       |
| 1  | Ram   | one, five  |

Answer 1

Your tags should have a INTEGER[] type.

CREATE TABLE users(
   id SERIAL,
   fname VARCHAR(50),
   tags INTEGER[]
);

Then,

SELECT I.id, I.fname, array_agg(J.name) 
FROM users I 
LEFT JOIN tags J 
ON J.id = ANY(I.tags) 
GROUP BY fname,I.id ORDER BY id

should work. See sqlfiddle

This question may help.