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"]');
SELECT DISTINCT tmp.city
FROM table_cities c,
JSON_TABLE(
c.cities,
'$[*]'
COLUMNS(
city VARCHAR(10) PATH '$[0]'
)
) tmp;
Records: 5 Duplicates: 0 Warnings: 0
| city |
|---|
| madrid |
| london |
| paris |
| rome |
| berlin |