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
– MindStalker, Commented Jan 26, 2010 at 3: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
– Dan Beam, Commented Jan 26, 2010 at 4:35
Possible duplicate of How create json format with group-concat mysql? — e4c5
– e4c5, Commented Nov 26, 2016 at 15:07

MindStalker · Accepted Answer · 2010-01-26 19:25:51Z

0

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

answered Jan 26, 2010 at 19:25

MindStalker

14.9k3 gold badges28 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dan Beam Over a year ago

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).

Collectives™ on Stack Overflow

JSON VIEW using GROUP_CONCAT question

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related