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

Question

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']

doesn't appear to be anything in the data that would guarantee a particular order of elements in the array. from the data shown, looks like 102 ['4g','2g','4g','3g'] (the elements in a different order ) would be equally valid. (before someone says this is not an answer to the question that was asked, that's why i posted this as a comment and not an answer) — spencer7593
– spencer7593, Commented Apr 30, 2020 at 13:47

GMB · Accepted Answer · 2020-04-30 13:15:10Z

3

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

answered Apr 30, 2020 at 13:15

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Subbu VidyaSekar · Accepted Answer · 2020-09-04 17:17:50Z

1

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

edited Sep 4, 2020 at 17:17

Subbu VidyaSekar

2,6413 gold badges26 silver badges45 bronze badges

answered Apr 30, 2020 at 13:54

Ganesh Kumar

111 bronze badge

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related