MySQL and JSON data structures
Having been working with JSON data structures recently our thoughts turned to how this could reasonably be handled by SQL queries in the production of items such as reports etc. Leaving aside the question of how one would ‘know’ and handle the various constituents in the specific JSON object on a generic basis, we looked specifically at what was currently available.
Coming from a strong Oracle background we were familiar with the use of Java within the Oracle database and indeed have made use of it ourselves in the past, but our specific interest this time was MySQL and its use by Joomla, and we were not aware of any feature implementing Java with the MySQL database.
We have long believed that making use of the features of the database made a lot of sense, as opposed to handling the data at the application layer. Agreed, with MySQL the benefits are not quite as good as one might expect with other databases such as Oracle due to the nature of how MySQL interprets and re-evaluates queries. This article goes into some points on the wisdom of using database procedures for handling data from an engineering viewpoint. The points are well made especially those regarding development tools, however one generally makes use of the best tool for the job and if one tends to be experienced in the use of database procedures etc., it is only natural that one would automatically be swayed in the use of the best tools for that specific task. It was for this reason we wanted to know more about JSON data handling possibilities within the database itself.
We came across a very interesting article by Ulf Wendel concerning MySQL functions for the handling of JSON data in release 5.7. It is well written and reasonably recent. The downside is likely to be that the feature is not likely to be available as part of a standard MySQL release for some time, and even when it is the number of sites with the ‘latest’ version of MySQL available to use it is going to be very low for quite some time. Considering that MySQL 5.6.16 is the latest community version, and that there are still a lot of MySQL 5.0 and 5.1 installations out there, this would effectively make it virtually impossible to implement the feature in a Joomla ‘application’ for general release.
Another alternative might be the use of the Common Schema which works for versions of MySQL 5.1 and above. This is a downloadable script that can be loaded into the database. Whilst this implementation is not going to be as ‘extensive’ in functionality as the earlier possibility, it does at least raise the chance of having the parts of the code which we would be interested in installed as part of the Joomla component.
At present we have not really decided one way or the other and are really just looking around to see the ‘lie of the land’. The suspicion is that we will eventually decide to extract the data from the database and then process it within PHP to present it is a form suitable for display, which is after all the current way in which web pages display the JSON information.
This tends to be reinforced by the knowledge that not all web hosting suppliers permit their clients to create database objects such as triggers, procedures and even views. Indeed we have even had to implement two mechanisms in some Joomla components to handle the specific situation where this was true. One of the problems with the desire to make use of the ‘latest’ enhancements I guess.