JSON VIEW using GROUP_CONCAT question

Question

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.

Answer 1

I've tested some of this out and can't find any fault.

Try creating a VIEW like

CREATE VIEW tn_view AS
SELECT  tn.id, tn.name, t1.id, t2.id

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

THEN

SELECT CONCAT(
  "{",
     "\"some_list\":[", GROUP_CONCAT( DISTINCT t1.id ), "],",
     "\"other_list\":[", GROUP_CONCAT( DISTINCT t2.id ), "],",
  "}"
) cool_json
FROM tn_view
GROUP BY id ORDER BY name