mysql_json - a MySQL UDF for parsing JSON

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';