Question 22046 in Mysql Question posted in
The official MySQL documentation can be found here.

How to open and transpose list in MySQL

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

Tags:

2

Answers


  1. 0

    @Ruslan Pylypiuk

    Postgresql soultion:

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

    Mysql soultion : refer SQL split values to multiple rows

    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search