MySQL: Guide to Opening and Transposing Lists

Question

I have a given dataframe: list of id numbers 2[1,2,5,6,7]5[1,2,13,51,12] where one column is just the id and the other is a list of numbers like I As previously obtained from a JSON file, is there a way to convert it to this format using just MySQL? List of id numbers 21222526275152513551512 I know it can be done easily using Python and pandas, but in this case I just need to use MySQL, and I don't really know how to transpose a list in MySQL

P粉786432579 · Answer

@Ruslan Pylypiuk

Postgresql Soul:

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

Mysql Soul: ReferenceSQL split value into multiple rows

P粉883223328 · Answer

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	Number list
2	1
2	2
2	5
2	6
2	7
5	1
5	2
5	13
5	51
5	12

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