JSON is awesome because it provides an amazing way for you to send information from one place to another and be guaranteed that it can be decoded by whatever language is on the other end. It's also awesome because it's schema-less and can easy adapt to hold any data. It just sucks when someone starts entering it into a database and you need to get it out.

Please note that I'm not recommending you EVER put JSON into a database column. This is a quick fix but it's going to bite you in the butt later and someone is going to have to clean it up. :-)

One of the products I inherited has several JSON fields in a MySQL database that contain important information that we needed to created a report on. The end goal is to rewrite the sections of the code that read to and write from these columns but they're important and most of the site actually uses it (we found over a thousand places where it's referenced). As a quick fix we found mysqludf_json_extract which allows you to extract specific information from JSON encoded data in a database.

My favorite part about this project is it's description:

if you thought it was a good idea to put json blobs in mysql and are living with the consequences

It reminds me of this comment about regular expressions:

Some people, when confronted with a problem, think "I know, I'll use regular expressions." Now they have two problems.

Installing

I had to install the mysql header files (libmysqlclient-dev) and cmake before I got started:

sudo apt-get install cmake libmysqlclient-dev 

mysqludf_json_extract hasn't been updated in a while (4 years when I wrote this) and it has a dependency(yajl) that has been updated recently. You need to make sure you build the 1.x branch.

git clone https://github.com/lloyd/yajl.git 
cd yajl/ 
git checkout 1.x 
cmake . 
sudo make install

I also found that on Ubuntu you need to copy the library to /usr/lib for it to work correctly:

sudo cp /usr/local/lib/libyajl.so.1 /usr/lib/libyajl.so.1

Finally, when you install you need to specify MYSQL_HOME and MYSQL_CONFIG

git clone https://github.com/dkf/mysqludf_json_extract.git 
cd mysqludf_json_extract 
set MYSQL_HOME = /usr/lib/mysql/ 
set MYSQL_CONFIG = /usr/lib/mysql/ 
cmake . 
make 
make test 

Again, also found that on Ubuntu you need to copy the library to /usr/lib for it to work correctly:

sudo cp src/libmysqludf_json_extract.so /usr/lib/mysql/plugin/

Finally, you need to setup the function in MySQL.

mysql> drop function json_extract;
Query OK, 0 rows affected (0.00 sec)
mysql> create function json_extract returns string soname 'libmysqludf_json_extract.so';
Query OK, 0 rows affected (0.00 sec)

Then you can use it just like any other function:

mysql> select json_extract("a", val) result from foo;

We've been using mysqludf_json_extract for several months now and the only real problem we've run into is if we try to perform a where on a field inside a JSON blob it can stop the query. We just told people it doesn't work that way. :-)

In closing, don't do this. It's a hung pain in the ass.