JSON VIEW using GROUP_CONCAT question
- by Dan Beam
Hey DBAs and overall smart dudes. I have a question for you.
We use MySQL VIEWs to format our data as JSON when it's returned (as a BLOB), which is convenient (though not particularly nice on performance, but we already know this).
But, I can't seem to get a particular query working right now (each row contains NULL when it should contain a created JSON object with the values of multiple JOINs).
Here's the general idea:
SELECT CONCAT(
"{",
"\"some_list\":[", GROUP_CONCAT( DISTINCT t1.id ), "],",
"\"other_list\":[", GROUP_CONCAT( DISTINCT t2.id ), "],",
"}"
) cool_json
FROM table_name tn
INNER JOIN ( some_table st ) ON st.some_id = tn.id
LEFT JOIN ( another_table at, another_one ao, used_multiple_times t1 )
ON st.id = at.some_id AND
at.different_id = ao.different_id AND
ao.different_id = t1.id
LEFT JOIN ( another_table2 at2, another_one2 ao2, used_multiple_times t2 )
ON st.id = at2.some_id AND
at2.different_id = ao2.different_id AND
ao2.different_id = t2.id
GROUP BY tn.id ORDER BY tn.name
Anybody know the problem here? Am I missing something I should be grouping by? It was working when I was only doing 1 LEFT JOIN & GROUP_CONCAT, but now with multiple JOINs / GROUP_CONCATs it's messing it up.
When I move the GROUP_CONCATs from the "cool_json" field they work as expected, but I'd like my data formatted as JSON so I can decode it server-side or client-side in one step.
