Mysql Retrieve Data From JSON, MySQL

Language: Layout:

DROP TABLE IF EXISTS JSON_TABLE;
DROP TABLE IF EXISTS SPLIT_TABLE;
DROP VIEW IF EXISTS SPLIT_VIEW;

/* APPENDIX 1-A: 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-B: 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;


absolute service time: 0,34 sec