pankaj ghadge pankaj ghadge - 2 months ago 7
MySQL Question

How to retrieve JSON data from mysql?

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:

select getJson("quota") as quota, client_id from client_services where service_id =1;


OR

Can I normalize client_services table further?

Services:

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


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 |
+----+------------+----------------------------------+------------------------+


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 } |
+-----+-----------+------------+-------------------------------------------------------------------------------------------+


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

Late for the party. But hope it helps for future viewers.

Although it is highly recommended to not to do so, I am putting this solution along. I once used (worked for me at least with mysql 5.7) to migrate json containing mysql data to relational tables.

You can use:

SELECT
json_extract(columnName, '$.jsonKey') AS someColumnName
FROM your_table

Example, I had a column gender_mix in table population_stats as:

{"male":"11","female":"23","other":"0"}

I extracted as:

SELECT 
  json_extract(gender_mix, '$.male') AS gender_mix_male,
  json_extract(gender_mix, '$.female') AS gender_mix_female,
  json_extract(gender_mix, '$.other') AS gender_mix_other
FROM population_stats

Please note again, this operation can be expensive - I used for data migration and so should you.