Return valid JSON from MySQL

4 years ago · 1 MIN READ

Grabbing nested data from a MySQL server is a bitch. You will have to use the GROUP_CONCAT function - and there's no way to get around looping through and parsing your results. Usually when I need to this I will GROUP_CONCAT the row IDs, and then run a query for each ID to get the properties I need.

This sucks, because more queries means longer execution time. This nifty solution will format your GROUP_CONCAT field as a valid JSON array. Note: You'll still have to loop through your results and json_decode that shiat.

SELECT
    c.id testicle_id ,
    c.`name` testicle_nickname ,
    concat( '[' ,
        GROUP_CONCAT(
        '{ "id":' ,
            u.id ,
        ', "first_name":"' ,
            u.first_name ,
        '", "last_name":"' ,
            u.last_name ,
        '", "gender":"' ,
            u.gender ,
        '", "likes_balls":' ,
            u.likes_balls ,
        ', "likes_dicks":' ,
            u.likes_dicks ,
        ', "teabagged_cnt":' ,
            u.teabagged_cnt ,
        ', "teabag_start":"' ,
            ca.teabag_start ,
        '", "teabag_end":"' ,
            ca.teabag_end ,
        '"}' 
        ),
    ']' ) teabagging_targets
FROM
    teabag_tuesday c
INNER JOIN 
    teabagging_targets u ON u.id = c.user_id
WHERE
NOW() BETWEEN teabag_start AND teabag_end 
AND ( likes_balls = 1 OR likes_dicks = 1 )
GROUP BY c.id

···