thomasfrank.se

MySQL to JSON

Januari 23, 2007

I confess - 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.

SELECT 
	CONCAT("[",
		GROUP_CONCAT(
			CONCAT("{username:'",username,"'"),
			CONCAT(",email:'",email),"'}")
		)
	,"]") 
AS json FROM users;
[
	{username:'mike',email:'mike@mikesplace.com'},
	{username:'jane',email:'jane@bigcompany.com'},
	{username:'stan',email:'stan@stanford.com'}
]

You could build XML as well

Of 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.
[comments]