TokuDB and JSON data in MySQL 5.7

MySQL recently added support for JSON data with MySQL 5.7.8.  It would be cool to store JSON data in TokuDB tables.

First, I had to get TokuDB running on MySQL 5.7.8.   TokuDB currently runs on Percona Server 5.6, and various flavors of MariaDB.  The only issues porting TokuDB to MySQL 5.7 were adopting the changes to various internal APIs that storage engines use.  Since I did not make any patches to the MySQL code,  some TokuDB features including clustered secondary keys and selection of various compression algorithms for TokuDB tables are missing.  Compression with zlib is always used.

Second, I decided to store JSON data in a TokuDB blob since the JSON field class is derived from the blob field class.  The changes to support JSON as blobs in TokuDB are minimal.

Here is an example from Hatlen's blog of storing JSON data in a TokuDB table.

mysql> create table employees (data JSON) engine=tokudb;

Query OK, 0 rows affected (0.06 sec)

mysql> insert into employees values ('{"id": 1, "name": "Jane"}');

Query OK, 1 row affected (0.02 sec)

mysql> insert into employees values ('{"id": 2, "name": "Joe"}');

Query OK, 1 row affected (0.00 sec)

mysql> select * from employees;

+---------------------------+

| data                      |

+---------------------------+

| {"id": 1, "name": "Jane"} |

| {"id": 2, "name": "Joe"}  |

+---------------------------+

2 rows in set (0.00 sec)

mysql> select json_extract(data, '$.name') from employees;

+-----------------------------+

| jsn_extract(data, '$.name') |

+-----------------------------+

| "Jane"                      |

| "Joe"                       |

+-----------------------------+

2 rows in set (0.00 sec)

If you want to play with JSON data in TokuDB tables, here is a script to build MySQL 5.7.8 with TokuDB.  Note that all of these components are experimental and only some of the TokuDB tests pass.