I’m converting some php scripts into mysql stored procedures and have come across the usage of php’s json_decode() on a result set’s data. It occurs on a field which is stored as a mediumtext,utf8,utf8_unicode_ci
How can I perform the ‘json_decode()’ process using pure MySQL?
Sample data from the field:
{“93489”:{“X1”:{“net”:164,”vat”:33.6},”X2″:{“net”:0,”vat”:0}}}
Advertisement
Answer
You can use JSON_EXTRACT function:
mysql> SELECT c, JSON_EXTRACT(c, "$.id"), g > FROM jemp > WHERE JSON_EXTRACT(c, "$.id") > 1 > ORDER BY JSON_EXTRACT(c, "$.name"); +-------------------------------+-----------+------+ | c | c->"$.id" | g | +-------------------------------+-----------+------+ | {"id": "3", "name": "Barney"} | "3" | 3 | | {"id": "4", "name": "Betty"} | "4" | 4 | | {"id": "2", "name": "Wilma"} | "2" | 2 | +-------------------------------+-----------+------+ 3 rows in set (0.00 sec)