Skip to content

Instantly share code, notes, and snippets.

@ti-ka
Created March 10, 2017 16:34
Show Gist options
  • Save ti-ka/d0b264324c68fb81743035d98b9cbb8c to your computer and use it in GitHub Desktop.
Save ti-ka/d0b264324c68fb81743035d98b9cbb8c to your computer and use it in GitHub Desktop.
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.
/* 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;
@motsmanish
Copy link

Thanks. Please replace BAD_TABLE by JSON_TABLE.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment