How to retrieve JSON data from MySQL?

Solution 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.

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

Solution 3:

Firstly you should be aware that your model above is not in First Normal Form, meaning you should have only a single value in each field. However, this definition is dependent on your application query processing needs.

So, if all you want is to put a bunch of JSON data in a field and return it to the application as is, then it's fine. You could return the whole JSON data and let the application pick the JSON attributes it wants.

But if you have queries, as in your case, having criteria or field expressions looking into the details of the JSON data, then it's definitely a no-go. It will be a nightmare in query complexity and slow processing.

You can of course further normalise your tables to fully replace the JSON data structure. However, if your application needs a flexible schema, perhaps the main reason to use NOSQL DBs, but you are stuck with MySQL, there are two solutions:

a) use MySQL 5.6 (or MariaDB v. ??) for NoSQL support (I haven't looked into details) http://www.computerworld.com/s/article/9236511/MySQL_5.6_tackles_NoSQL_competitors MariaDB's dynamic columns: https://kb.askmonty.org/en/dynamic-columns/

b) use mysql without an explicit schema, see here for a very well done solution, which has no scalability problems: http://backchannel.org/blog/friendfeed-schemaless-mysql

Solution 4:

Yes , you can definitely retrieve JSON values using MySQL query(using JSON_EXTRACT() function).

lets take your table that contains json (table client_services here) :

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

To select each JSON fields , run this query :

SELECT 
    id, client_id, service_id,
    json_extract(service_values, '$.quota') AS quota,
    json_extract(service_values, '$.free_quota') AS free_quota,
    json_extract(service_values, '$.total_accounts') AS total_accounts,
    json_extract(service_values, '$.data_transfer') AS data_transfer
FROM client_services;

So the output will be :

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

Hope this helps!