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.

Have you tried running this query directly into the database. Do you get any warnings in the output about group_concat? group_concat can easily hit its natural limit of 1024 characters. (Which can be increased) — MindStalker, Jan 26 '10 at 03:57
I've already `set global group_concat_max_len = 8192;` (which is enough), but even when this happens, it just cuts off the text (I'm only getting `NULL`). good guess, though, that has been the issue before (messing up my JSON) — Dan Beam, Jan 26 '10 at 04:35
Possible duplicate of [How create json format with group-concat mysql?](http://stackoverflow.com/questions/12511933/how-create-json-format-with-group-concat-mysql) — e4c5, Nov 26 '16 at 15:07

score 0 · Answer 1 · answered Jan 26 '10 at 19:25

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

yes, making a VIEW and then grabbing that data would work, but I'd like to do it all inline (we already have too many VIEWS lying around). — Dan Beam, Jan 29 '10 at 18:29

JSON VIEW using GROUP_CONCAT question

1 Answers1