MySQL to JSON

Courtesy:  Thomas Frank

MySQL 4.1 or later:  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;

 

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.

Advertisements