MySQL to JSON - this is cool!

I've used MySQL's amazing "GROUP_CONCAT()" function many times, it's an absolute life saver in very specific situations, and it's supa-fast. I've attempted to use it to essentially store sub-query data in a string. This usually makes the resulting code less than ideal when attempting to parse the string back into an iterate-able data set. Today I needed this once again and I wasn't looking forward to it.

Recently I've dealt a lot more with JSON data sets with AJAX features and I've fallen in love with JSON. I love how universal it has become to pass data between programming languages, be that client side Javascript to/from server-side or between server-side languages in web services, whatever. It's always great. So I was thinking to my self, 'What if I could get by multidimensional data from the database into a JSON string?'. So I started googling "SQL to JSON" and I found this guys blog post: http://www.thomasfrank.se/mysql_to_json.html. (Thank you Tomas Frank! Wow, nearly 5 years ago. Ha!)  It was great. And all the comments were helpful as well. So I wrote my own in similar fashion. See my pseudo-sql below:

select f.fileid, f.parentfileid, f.filename, f.filesize, f.onloadon, f.uploadby, u.firstname, u.lastname,
    IFNULL((select CONCAT('[',
                    GROUP_CONCAT(
                        CONCAT('{"fileid":"', f2.fileid, '","filename":"', f2.filename, '","filesize":"', f2.filesize, '","onloadon":"', f2.onloadon, '","uploadby":"', f2.uploadby, '","firstname":"', u2.firstname, '","lastname":"', u2.lastname, '"}')
                    SEPARATOR ','),
                 ']')
            from filestable f2 LEFT OUTER JOIN userstable u2 ON u2.userid = f2.uploadby
            where f2.parentfileid = f.fileid
            and f2.deleted = 0
            order by f2.uploadon ASC),
    '[]')  as versions
from filestable f
  LEFT OUTER JOIN userstable u ON u.userid = f.uploadby
where f.parentfileid = 0
and f.deleted = 0

Essentially I'm querying for files and getting any past versions of each file packed into a JSON formatted string.

Then in the Coldfusion code you call: DeserializeJSON(myquery.versions) and you have an ARRAY of STRUCTURES in Coldfusion, each struct is a subquery row of data containing old file servsions. This is how we get 3 dimensional data from a 2 dimentional database query.

*** I hope the single and double quotes are easily readable in the code sample above. SQL strings are in single quotes and the JSON keys and values are all in double quotes.

*** It's important to notice the IFNULL() function around the sub-query because without that, if there are no records, it returns a NULL value. In Coldfusion that becomes an empty string. That's bad when we were expecting an array. I learned that the hard way when it was erroring out on my ArrayLen(variables.filevs) code, see below.

*** Another important note is to sort the sub-query Ascending by date. The reason for this specification is so that we can more easily loop over the array backwards to display the most recent versions at the top and the oldest at the bottom.

This is how the resulting Coldfusion code might look:

<cfloop query="variables.myquery">
    <!--- html here to display each file (current version) --->
    <cfset variables.filevs = DeserializeJSON(variables.myquery.versions) />
    <cfloop from="#ArrayLen(variables.filevs)#" to="1" step="-1" index="variables.LLfilev">
        <cfset variables.filevdata = variables.filevs[variables.LLfilev] />
        <!--- html here to display the data of each old-versions of each file --->
    </cfloop>
</cfloop>

Clean and simple. You've gotta love that. Fast too! Much better then getting all files, parent and old-versions, and then running a few query-of-query's in our server-side code to first get the parent records (current vertsions) and then get the old-versions with a query inside each loop. Without this, QoQ may have been the only solution. This new solution is *SO* much better, faster, and cleaner.