If you have saved data into a field as json, you can use mysql to retrieve each field. It may be useful in migrating database when you have decided that using JSON in a database was not a good idea.

Retrive Json Data from Mysql.sql

/* APPENDIX 1-B: CLEANUP
			This makes this sql query re-run able */
            
DROP TABLE IF EXISTS JSON_TABLE;
DROP TABLE IF EXISTS SPLIT_TABLE;
DROP VIEW IF EXISTS SPLIT_VIEW;

/* APPENDIX 1-B: Prepare TABLE
			Let's say this is an example table */
            
CREATE TABLE JSON_TABLE (
  ID INT NOT NULL AUTO_INCREMENT,
  CITY VARCHAR(255) NOT NULL,
  POPULATION_JSON_DATA VARCHAR(1000) NOT NULL,
  PRIMARY KEY (ID)
 );
 
/* APPENDIX 1-C: Prepare Data
		Insert some data */ 
        
INSERT INTO JSON_TABLE (CITY, POPULATION_JSON_DATA) VALUES 
('LONDON', '{"male" : 2000, "female" : 3000, "other" : 600}'),
('NEW YORK', '{"male" : 4000, "female" : 5000, "other" : 500}');

SELECT * FROM JSON_TABLE;

/* APPENDIX 2-A: Raw Select
			  Select Json Into Desired Columns */
              
SELECT 
	ID, CITY,
	json_extract(POPULATION_JSON_DATA, '$.male') AS POPULATION_MALE,
	json_extract(POPULATION_JSON_DATA, '$.female') AS POPULATION_FEMALE,
	json_extract(POPULATION_JSON_DATA, '$.other') AS POPULATION_OTHER
FROM JSON_TABLE;

/* APPENDIX 2-B: Migration
		      Insert to table so that you can make better use of resources: */

CREATE TABLE SPLIT_TABLE (
  ID INT NOT NULL AUTO_INCREMENT,
  CITY VARCHAR(255) NOT NULL,
  MALE_POPULATION int default 0,
  FEMALE_POPULATION int default 0,
  OTHER_POPULATION int default 0,
  PRIMARY KEY (ID)
 );
 
INSERT INTO SPLIT_TABLE 
SELECT 
	ID, CITY,
	json_extract(POPULATION_JSON_DATA, '$.male') AS POPULATION_MALE,
	json_extract(POPULATION_JSON_DATA, '$.female') AS POPULATION_FEMALE,
	json_extract(POPULATION_JSON_DATA, '$.other') AS POPULATION_OTHER
FROM BAD_TABLE;

SELECT * FROM SPLIT_TABLE;


/* APPENDIX 2-C: Create View
		      Insert to table so that you can make better use of resources: */

CREATE VIEW SPLIT_VIEW AS
SELECT 
	ID, CITY,
	json_extract(POPULATION_JSON_DATA, '$.male') AS POPULATION_MALE,
	json_extract(POPULATION_JSON_DATA, '$.female') AS POPULATION_FEMALE,
	json_extract(POPULATION_JSON_DATA, '$.other') AS POPULATION_OTHER
FROM BAD_TABLE;

SELECT * FROM SPLIT_VIEW;