Skip to content

Instantly share code, notes, and snippets.

@ti-ka

ti-ka/Retrive Json Data from Mysql.sql

Created March 10, 2017 16:34
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save ti-ka/d0b264324c68fb81743035d98b9cbb8c to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save ti-ka/d0b264324c68fb81743035d98b9cbb8c to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. Tika Pahadi created this gist Mar 10, 2017.
    74 changes: 74 additions & 0 deletions Retrive Json Data from Mysql.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,74 @@

    /* 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;