PostgreSQL JOIN with array type with array elements
I have two table 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 Result:
| 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 |
+3
source to share
1 answer
Yours tags
must be of type INTEGER[]
.
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
must work. See the sqlfiddle
This question might help.
+2
source to share