Somewhat related to (or rather not related to) やったーJavaScriptの動くMySQLできたよー - 愛と勇気と缶ビール, I have written a MySQL UDF that parses JSON strings.
The UDF introduces one function: json_get, that parses a JSON object or an array and returns one of the properties.
SELECT json_get('{"a":1}', 'a') => 1
SELECT json_get('{"a":1}', 'b') => NULL
SELECT json_get('[1,2,3]', 2) => 3
SELECT json_get('{"a":[2]}', 'a', 0) => 2
By using to the UDF it is possible to write queries accessing the properties of JSON objects stored in MySQL.
SELECT json_get(data,'title') FROM message WHERE id=1;
SELECT id,data FROM message WHERE json_get('data','from')='hidek';
Source code of the UDF can be found at github.com/kazuho/mysql_json. The installation process may vary, but I did the following for my ubuntu server.
% g++ -shared -fPIC -Wall -g mysql_json.cc -o mysql_json.so % sudo cp mysql_json.so /usr/lib/mysql/plugin % mysql -u root mysql> create function json_get returns string soname 'mysql_json.so';
