Storing heterogeneous json objects in a single MySQL table?

Question

What is the recommended way to store heterogeneous json strings in the same MySQL table? I have other tables in this MySQL database that store information other than JSON objects, so I would like to store these JSON objects in the same database in the best way.

I want to save json strings from a Perl script into a MySQL table, which will not only not have the same values, but will also have different hierarchical structures in different json objects. I consider their storage as lines or drops, as well as some minimal metadata for each record. For instance:.

CREATE TABLE `entry` ( `entry_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `file` varchar(1023) DEFAULT NULL, `json` blob DEFAULT NULL, `update_timestamp` TIMESTAMP DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`entry_id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1; 

Are there any tools or libraries that I should consider before doing this directly with Perl and MySQL? I wonder if there is anything that does the opposite of what DBIx :: JSON does for MySQL SELECT queries ...

Answer 1

I found that Postgresql 9.3 has better JSON capabilities than the MySQL versions I use:

http://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.3#JSON%3a_Additional_functionality

http://www.postgresql.org/docs/9.3/static/functions-json.html

Answer 2

Json objects in the database are great until you want to request its contents.

However, the type of storage is worth discussing. I would either compress the entire ROW in InnoDB with (ROW_FORMAT = COMPRESSED KEY_BLOCK_SIZE = 8), or execute it in an application, compress only JSON and put it in binary format.

With the first option (the application handles TEXT, MySQL handles compression), I would prefer the json column to have a text type (TEXT, LONGTEXT, MEDIUMTEXT, TINYTEXT).

With the second version (the application handles compression, MySQL only sees the binary file), I would, of course, use the blob format (TINYBLOB, BLOB, MEDIUMBLOB or LONGBLOB).

Both options are valid. It depends on your personal priorities. Transferring compression to an application is an easy way to scale, but complexity. Although MySQL takes care of compression, it is transparent and easy to configure, but it puts some pressure on the processor (which, by the way, is rarely a bottleneck for databases).