How to retrieve JSON data from MySQL?

Question

I have following tables and their relationship. I am storing JSON data in client_services table. Is their any way to retrieve JSON values using MySQL query like this:

SELECT getJson("quota") as quota,        client_id FROM client_services WHERE service_id = 1;      

Or can I normalize client_services table further?

Table Services:

+----+-----------------------+--------------------------------------------------------+ | id | name                  | description                                            | +----+-----------------------+--------------------------------------------------------+ |  1 | MailBox               |                                                        | |  2 | SMS                   |                                                        | |  3 | FTP                   |                                                        | +----+-----------------------+--------------------------------------------------------+ 

Table service_features:

+----+------------+----------------------------------+------------------------+ | id | service_id | name                             | description            | +----+------------+----------------------------------+------------------------+ | 10 |          1 | Forwarding                       | Forward Mail           | | 11 |          1 | Archive                          | Archive Mail           | | 12 |          1 | WebMail                          | NULL                   | | 13 |          1 | IMAP                             | NULL                   | | 14 |          2 | Web SMS                          | NULL                   | +----+------------+----------------------------------+------------------------+ 

Table client_services:

+-----+-----------+------------+-------------------------------------------------------------------------------------------+ | id  | client_id | service_id | service_values                                                                            | +-----+-----------+------------+-------------------------------------------------------------------------------------------+ | 100 |      1000 |          1 |{ "quota": 100000,"free_quota":20000,"total_accounts":200,"data_transfer":1000000}         | | 101 |      1000 |          2 |{ "quota": 200 }                                                                           | | 102 |      1000 |          3 |{ "data_transfer":1000000}                                                                 | | 103 |      1001 |          1 |{ "quota": 1000000,"free_quota":2000,"total_accounts":200,"data_transfer":1000000}         | | 104 |      1001 |          2 |{ "quota": 500 }                                                                           | | 105 |      1002 |          2 |{ "quota": 600 }                                                                           | +-----+-----------+------------+-------------------------------------------------------------------------------------------+ 

Table client_feature_mappers:

+-----+-------------------+--------------------+-----------+ | id  | client_service_id | service_feature_id | client_id | +-----+-------------------+--------------------+-----------+ |10000|                100|                 10 |       1000| |10001|                100|                 11 |       1000| |10002|                100|                 12 |       1000| |10003|                100|                 13 |       1000| |10004|                101|                 14 |       1000| |10005|                103|                 10 |       1001| |10006|                101|                 11 |       1001| |10007|                101|                 12 |       1001| |10008|                101|                 13 |       1001| |10009|                105|                 14 |       1002| +-----+-------------------+--------------------+-----------+ 

Answer 1

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.

Answer 2

You can use MySQL function SUBSTRING_INDEX to break down the JSON string:

SELECT  SUBSTRING_INDEX(  SUBSTRING_INDEX(   SUBSTRING_INDEX( service_values, 'quota', -1),   '": ', -1),  ' ', 1) AS quota, client_id FROM client_services WHERE service_id=1;