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 | WebMai…

Accepted Answer

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

- Migration of table to split database (See Appendix 2-B in gist)

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

How to retrieve JSON data from MySQL?

Solution

Problem