CODEWITHSUNDEEP
mysqlsqlarraysselect
Abdul K Shahid
Abdul K Shahid

Reputation: 528

How is it possible to select the value as array using sql?

While selecting the values in mysql from a table, for example groups g, which has the columns g.id, g.name

there is another table called group_members

I need to fetch all the groups with all the members

So the sql will return the result as

1. group_id : 1, group_name : john, members_ids : [12,14]
2. group_id : 2  group_name : jill, members_ids : [12,13,14,15]



SELECT g.id as group_id, 
g.name as group_name,
ug.members_ids
FROM groups g
LEFT JOIN group_members gm on gm.user_id = [Userid]

GROUPS TABLE

id | name
-----------
1 | group 1
2 | group 2

group_members Table

id | group_id | user_id
1  | 1        | 12
2  | 1        | 14
3  | 2        | 12
4  | 2        | 13
4  | 2        | 13

Upvotes: 1

Views: 288

Answers (1)

D-Shih
D-Shih

Reputation: 46229

If I understand correctly you can try to use GROUP_CONCAT function

SELECT 
    g.id as group_id, 
    g.name as group_name,
    GROUP_CONCAT(gm.user_id SEPARATOR ',')  members_ids
FROM groups g
LEFT JOIN group_members gm on g.id = gm.group_id
GROUP BY 
    g.id, 
    g.name

or display as JSON array you can try to use JSON_ARRAYAGG function

SELECT 
    g.id as group_id, 
    g.name as group_name,
    JSON_ARRAYAGG(gm.user_id)  members_ids
FROM groups g
LEFT JOIN group_members gm on g.id = gm.group_id
GROUP BY 
    g.id, 
    g.name

Upvotes: 2

Related Questions