get all keys from json column and convert to rows-CodePudding

I have a table like the following, where cities is a JSON column

ID	cities
1	["madrid"]
2	["london", "madrid", "paris"]
3	["london", "paris"]
4	["london"]
5	["rome", "berlin"]

I would like to convert that column to rows, where all individual city gets its own row

city
madrid
london
paris
rome
berlin

I've tried something like

SELECT distinct JSON_VALUE(JSON_EXTRACT(cities, '$[*]'), '$[*]') as cities FROM table_cities

but I get this instead

city
madrid
NULL
london

CodePudding user response：

If you are using MySQL version 8.0, you can try using the function JSON_TABLE (tested on dbfiddle)

SELECT DISTINCT tmp.city
FROM table_cities c,
JSON_TABLE(
  c.cities,
  '$[*]'
  COLUMNS(
  city VARCHAR(10) PATH '$[0]'
  )
) tmp;

For MariaDB 10.4.2, you can try this. (tested on dbfiddle)

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
SELECT DISTINCT REPLACE(cities, '"', '') AS city 
FROM main_table
WHERE cities IS NOT NULL;