CODEWITHSUNDEEP
mysqlsqlarraysjsonaggregate-functions
Thivagar Moorthy
Thivagar Moorthy

Reputation: 233

How do I convert a column to an array in mysql?

I have a table of 2 columns, user_id and connection_type. Both the fields are not unique. One single user id can appear many times and one single connection type can appear many times. How will I group all the connection types to one single user id?

Table I have:

schema: 
user_id -- INT 
connection_type -- STRING

user_id connection_type 
101     4g 
102     3g 
101     4g 
101     2g 
102     2g 
101     4g 
102     4g 
101     4g 
102     4g 
101     4g

Table I need from the above:

user_id connection_type 
101     ['4g','4g','2g','4g','4g','4g'] 
102     ['3g','2g','4g','4g']

Upvotes: 3

Views: 10368

Answers (2)

Ganesh Kumar
Ganesh Kumar

Reputation: 11

you can use group_concat function in MySQL:

query is below:

select user_id,
group_concat(",",Connection_type) as connecttion_type
from table
group by user_id

Upvotes: 1

GMB
GMB

Reputation: 222482

MySQL does not natively support arrays - but if you want a JSON array you can do:

select user_id, json_arrayagg(connection_type) connection_types
from mytable
group by user_id

Upvotes: 3

Related Questions