How to get JSON data from mysql?

Question

I have the following tables and their relationships. I am storing json data in client_services table. This is their way of getting JSON values ​​using mysql query. eg:

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

OR

Is it possible to normalize the 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 1

Since many people asked this question to me personally, I thought that I was giving this answer a second revision. Here is the gist that has full SQL with SELECT, Migration and View Creation and live sql fiddle (availability is not guaranteed for fiddle).

Let's say you have a table (named: JSON_TABLE) as follows:

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

To select all json fields, you can:

 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 JSON_TABLE; 

which leads to:

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

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

• Transferring a table to a shared database ( See Appendix 2-B in the text)
• At least create a view (see Appendix 2-C)

Beware: 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 the MySQL SUBSTRING_INDEX function to split 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; 

Answer 3

First of all, you should know that your model is not in the first normal form above, that is, you should have only one value in each field. However, this definition depends on the needs of processing application requests.

So, if all you want is to put a bunch of JSON data in the field and return it to the application as is, then this is normal. You can return all the JSON data and let the application select the JSON attributes that it wants.

But if you have queries, as in your case, that have criteria or field expressions that parse the details of JSON data, then this is definitely not-go. It will be a nightmare in query complexity and slow processing.

Of course, you can normalize your tables to completely replace the JSON data structure. However, if your application needs a flexible schema, perhaps the main reason for using NOSQL DB, but you are stuck in MySQL, there are two solutions:

a) use MySQL 5.6 (or MariaDB v.) to support NoSQL (I did not look into the details) http://www.computerworld.com/s/article/9236511/MySQL_5.6_tackles_NoSQL_competitors MariaDB dynamic columns: https: // kb. askmonty.org/en/dynamic-columns/

b) use mysql without an explicit schema, see here for a very well made solution that does not have scalability issues: http://backchannel.org/blog/friendfeed-schemaless-mysql

Answer 4

The database is designed to store and retrieve data, not to format it. MySQL displays information in tabular form, and that is how it works. Therefore, if you want to have JSON, you need to convert this tabular data to JSON yourself, in your code.