How to open and transpose list in MySQL-CodePudding

I have a given dataframe:

id	listofnumbers
2	[1, 2, 5, 6, 7]
5	[1, 2, 13, 51, 12]

Where one column is just id, and the other one is list of number made like that which i got previously from JSON file, is there any way to get this into this format using only MySQL?

id	listofnumbers
2	1
2	2
2	5
2	6
2	7
5	1
5	2
5	13
5	51
5	12

I know it could be easily done using Python and pandas, but I only need to use MySQL in that case, and I do not really know how to transpose lists in MySQL like that

CodePudding user response：

You can use json_table()

create table myTable(id int, listofnumbers varchar(200));
insert into myTable values(2,   '[1, 2, 5, 6, 7]');
insert into myTable values(5,   '[1, 2, 13, 51, 12]');

select t.id, j.listofnumbers
from myTable t
join json_table(
  t.listofnumbers,
  '$[*]' columns (listofnumbers varchar(50) path '$')
) j;

id	listofnumbers
2	1
2	2
2	5
2	6
2	7
5	1
5	2
5	13
5	51
5	12

fiddle

CodePudding user response：

@Ruslan Pylypiuk

Postgresql soultion:

select 
id,regexp_split_to_table(listofnumbers,',')
from test

Mysql soultion : refer SQL split values to multiple rows