Building JSON from Mysql

I used to loop through my MySQL queries, in my server side language of choice, to build JSON. But there is a far better way that will save you some coding, add to simplicity and might even save some valuable server time. If you’re running MySQL 4.1 or later you can use the nifty function GROUP_CONCAT() together with the normal CONCAT() function to build all your JSON straight from your SQL query.

username	email
mike	mike@mikesplace.com
jane	jane@bigcompany.com
stan	stan@stanford.com

Our SQL table.

SELECT 
     CONCAT("[",
          GROUP_CONCAT(
               CONCAT("{username:'",username,"'"),
               CONCAT(",email:'",email),"'}")
          )
     ,"]") 
AS json FROM users;

A MySQL-query that returns JSON.

[
     {username:'mike',email:'mike@mikesplace.com'},
     {username:'jane',email:'jane@bigcompany.com'},
     {username:'stan',email:'stan@stanford.com'}
]

The returned JSON structure. You could build XML as wellOf course you could use GROUP_CONCAT() to build XML as well (or HTML tables for that matter). If you want to read up on how to disable the default comma separator, order things inside the group_concat etc you’ll find all the information you need in the MySQL manual, section 12.10.1.