3

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

Kazi Mohammad Ali Nur Romel
  • 15,301
  • 2
  • 13
  • 26
Ruslan Pylypiuk
  • 67
  • 5
  • If you have the original `id` along with the array in the same json document, something using [json_table](https://mariadb.com/kb/en/json_table/) might be possible in MariaDB. – danblack Nov 28 '22 at 03:18
  • Does this answer your question? [SQL split values to multiple rows](https://stackoverflow.com/questions/17942508/sql-split-values-to-multiple-rows) – Georg Richter Nov 28 '22 at 08:24

2 Answers2

3

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

Kazi Mohammad Ali Nur Romel
  • 15,301
  • 2
  • 13
  • 26
0

@Ruslan Pylypiuk

Postgresql soultion:

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

Mysql soultion : refer SQL split values to multiple rows

Learn Hadoop
  • 2,760
  • 8
  • 28
  • 60