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:
JavaScript
x
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)