How to retrieve JSON data from MySQL?

by

Since a lot of people have asked this question to me personally, I thought I would give this answer a second revision. Here is a gist that has the complete SQL with SELECT, Migration and View Creation and a live sql fiddle (availability not guaranteed for fiddle).

Let’s say you have table (named: TBL_JSON) like this:

 ID   CITY        POPULATION_JSON_DATA
-----------------------------------------------------------------------
 1    LONDON      {"male" : 2000, "female" : 3000, "other" : 600}
 2    NEW YORK    {"male" : 4000, "female" : 5000, "other" : 500}

To Select each json fields, you may do:

SELECT 
    ID, CITY,
    json_extract(POPULATION_JSON_DATA, '$.male') AS POPL_MALE,
    json_extract(POPULATION_JSON_DATA, '$.female') AS POPL_FEMALE,
    json_extract(POPULATION_JSON_DATA, '$.other') AS POPL_OTHER
FROM TBL_JSON;

which results:

ID  CITY      POPL_MALE  POPL_FEMALE   POPL_OTHER 
-----------------------------------------------------------------
1   LONDON    2000       3000          600
2   NEW YORK  4000       5000          500

This might be an expensive operation to run based on your data size and json complexity. I suggest using it for

  1. Migration of table to split database (See Appendix 2-B in gist)
  2. At least create a view (See Appendix 2-C in gist)

Watch out for: You may have json starting with double quotes (stringified):

"{"male" : 2000, "female" : 3000, "other" : 600}"

Tested with Mysql 5.7 on Ubuntu and Mac OSX Sierra.

More Related Contents:

  1. If two columns are 1 then make new column
  2. How can I return pivot table output in MySQL?
  3. Using LIMIT within GROUP BY to get N results per group?
  4. How to perform grouped ranking in MySQL
  5. Retrieving the last record in each group – MySQL
  6. Cast from VARCHAR to INT – MySQL
  7. Required to join 2 tables with their FKs in a 3rd table
  8. Does COUNT(*) always return a result?
  9. Ordering by specific field value first
  10. How to convert result table to JSON array in MySQL
  11. How to change the CHARACTER SET (and COLLATION) throughout a database?
  12. Connect By Prior Equivalent for MySQL
  13. Mysql::Error: Specified key was too long; max key length is 1000 bytes
  14. How to convert all tables from MyISAM into InnoDB?
  15. How can I get around MySQL Errcode 13 with SELECT INTO OUTFILE?
  16. Are there disadvantages to using a generic varchar(255) for all text-based fields?
  17. SQL Query to show nearest date?
  18. How to create relationships in MySQL
  19. Getting all parent rows in one SQL query
  20. Mysql Improve Search Performance with wildcards (%%)
  21. ALTER TABLE to add a composite primary key
  22. Replace Into Query Syntax
  23. How do I delete all the duplicate records in a MySQL table without temp tables
  24. Native JSON support in MYSQL 5.7 : what are the pros and cons of JSON data type in MYSQL?
  25. MySQL equivalent of Oracle’s SEQUENCE.NEXTVAL
  26. Why MySQL is giving error “Not allowed to return a result set from a function”?
  27. When is a timestamp (auto) updated?
  28. When to use a left outer join?
  29. MySQL vs. JSON – Why? [closed]
  30. mySQL query to search all tables within a database for a string?

Leave a Comment