How to extract data from Mysql column stored in serialized JSON column?

I have column in mysql DB which contains serialized data (used PHP JSON encode to serialize them).

Here is an example:

{"name":"Group ltd","email":"support@domain.o开发者_高级运维rg","auth":"Andrey Ucholnik"}

Is there a built in function in mysql to extract these values without PHP ? I mean to build query that will unserialize data.

Of course its possible to use combination of LOCATE and SUBSTR function to do that but I prefer something built in if possible.

---

There is no build in function in MySQL but by using small code in PHP you can easily do it as under:

<?php
    $json = '{{"generated": "2010-02-26T22:26:03.156866   blahblahblah   ": null, "thumbnail_url": "http://thumbs.mochiads.com/c/g/tetword-pro/_thumb_100x100.jpg", "screen4_url": "http://thumbs.mochiads.com/c/g/tetword-pro/screen4.png", "leaderboard_enabled": true, "resolution": "600x550", "width": 600}]}}';
    $out = json_decode($json, true);
    foreach($out["games"] as $game) {
        $name = addslashes($game[name]);
        $description = addslashes($game[description]);
        //Here you can use your mysql_insert code
        mysql_query("INSERT INTO games (name, description) VALUES('$name', '$description')") or die (mysql_error());
    } 
?>

---

There are no built-in MySQL functions to work with JSON, but here is a very simple stored function to extract values from JSON:

DELIMITER $$
CREATE FUNCTION JSON_EXTRACT(json TEXT, name CHAR(64))
RETURNS CHAR(64) DETERMINISTIC
BEGIN
    SET @namePos = LOCATE(name, json);
    IF @namePos = 0 THEN RETURN ''; END IF;
    SET @valuePos = LOCATE(':', json, @namePos) + 1;
    IF SUBSTR(json, @valuePos, 1) = '"' THEN
        SET @valuePos = @valuePos + 1;
        RETURN SUBSTR(json, @valuePos, LOCATE('"', json, @valuePos) - @valuePos);
    ELSE
        SET @valueBegin = TRIM(SUBSTR(json, @valuePos));
        SET @delim1 = LOCATE(' ', @valueBegin); SET @delim1 = IF(@delim1 = 0, 64, @delim1);
        SET @delim2 = LOCATE(',', @valueBegin); SET @delim2 = IF(@delim2 = 0, 64, @delim2);
        SET @delim3 = LOCATE('}', @valueBegin); SET @delim3 = IF(@delim3 = 0, 64, @delim3);
        RETURN LEFT(@valueBegin, LEAST(@delim1, @delim2, @delim3) - 1);
    END IF;
END$$

Usage example:

SELECT JSON_EXTRACT('{"a":"aa","b" : 1, "c": 3}', 'b') AS test;

Note that the function has many limitations. For example, it doesn't handle nested classes and key names shouldn't be included in the values.

---

Yes , you can definitely to it using JSON_EXTRACT() function in mysql.

lets take a table that contains JSON (table client_services here) :

+-----+-----------+--------------------------------------+
| id  | client_id | service_values                       |
+-----+-----------+------------+-------------------------+
| 100 |      1000 | { "quota": 1,"data_transfer":160000} |
| 101 |      1000 | { "quota": 2,"data_transfer":800000} |
| 102 |      1000 | { "quota": 3,"data_transfer":70000}  |
| 103 |      1001 | { "quota": 1,"data_transfer":97000}  |
| 104 |      1001 | { "quota": 2,"data_transfer":1760}   |
| 105 |      1002 | { "quota": 2,"data_transfer":1060}   |
+-----+-----------+--------------------------------------+

To Select each JSON fields , run this query :

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

So the output will be :

+-----+-----------+----------------------+
| id  | client_id | quota | data_transfer|
+-----+-----------+----------------------+
| 100 |      1000 |     1 |       160000 |
| 101 |      1000 |     2 |       800000 |
| 102 |      1000 |     3 |        70000 |
| 103 |      1001 |     1 |        97000 |
| 104 |      1001 |     2 |         1760 |
| 105 |      1002 |     2 |         1060 |
+-----+-----------+----------------------+

Hope this helps!