By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE table_cities (
id INTEGER,
cities JSON
);
INSERT INTO table_cities
VALUES
(1, '["madrid"]'),
(2, '["london", "madrid", "paris"]'),
(3, '["london", "paris"]'),
(4, '["london"]'),
(5, '["rome", "berlin"]');
CREATE TABLE main_table (
cities VARCHAR(1000)
);
-- create procedure
CREATE PROCEDURE proc1()
BEGIN
SET @index := 0;
SELECT @json_length := MAX(JSON_LENGTH(cities))
FROM table_cities;
REPEAT
INSERT INTO main_table (cities)
SELECT JSON_EXTRACT(cities,CONCAT("$[",@index,"]")) FROM table_cities;
SET @index = @index + 1;
UNTIL @index = @json_length
END REPEAT;
END;
-- call procedure
CALL proc1;
-- query result
Records: 5 Duplicates: 0 Warnings: 0
| @json_length := MAX(JSON_LENGTH(cities)) |
|---|
| 3 |
| city |
|---|
| madrid |
| london |
| rome |
| paris |
| berlin |