mysql doesnt return proper json

Question

I use mysql 5.7

I want to concatenate result from joined tables into single column. I use JSON_OBJECT function wrapped in GROUP_CONCAT function. here's query

SELECT r.id, r.name,
GROUP_CONCAT(
    JSON_OBJECT(
       'id', i.id, 'name', i.name, 'metrics', i.metrics, 'amount', ir.amount,
                  'cat_id', i.cat_id, 'cat_name', ic.name
    )) AS ingredients
FROM recipes r, ingredient_recipe_meta ir, ingredients i, ingredient_category ic
WHERE r.id = ir.recipe_id
AND i.id = ir.ingredient_id
AND i.cat_id = ic.id
GROUP BY r.id
LIMIT 1

column ingredients contains data from joined tables. the data retrieved correctly, but the problem is ingredients column did not convert to proper JSON. as you see it's kinda 'cut' at the end.

I also tried JSON_ARRAY, but now it doesn't convert to proper array. its seems like, the string is too big and it doesn't fit in column. is there way to solve this?

UPDATE

the problem must be in sting size which is not fit in column. I tried to select less columns from joined tables, and it works properly. maybe there is way to convert my json column to text 'on fly' while select? I tried to cast the column to text like

CAST(GROUP_CONCAT(...   AS TEXT)

but it gives syntax error

Is that a snapshot from PHPMyAdmin? it has a limit for field length to display, and when exceeded the data is cut, although MySQL is retrieving the full data. Editing the row shuold show the complete data. If not, try checking the results directly in MySQL data engine. — AngelGris, Feb 23 '18 at 21:18
yes, but it has nothing to do with phpMyAdmin. I dumped and also logged the result, its not proper JSON — devnull Ψ, Feb 23 '18 at 21:20
So, add the not proper JSON into your question so we can see what it is wrong with it, preferably the one you dumped. And please do not use this archaic join syntax. Use proper INNER|LEFT|RIGHT JOIN syntax. — Jorge Campos, Feb 23 '18 at 21:36
My wild guess is that it is something to do with some non ansi character on that database columns... — Jorge Campos, Feb 23 '18 at 21:40
did you read the question ? I indicated the version, I also add the print screen of JSON, it has nothing to do with phpMyAdmin, I got the same result in logs — devnull Ψ, Feb 23 '18 at 21:42
but why non ansi character should be a problem? all columns are in `utf8mb4_unicode_ci` collation, I guess the problem is that size of result string is too big — devnull Ψ, Feb 23 '18 at 21:47
See: [group_concat_max_len](https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_group_concat_max_len) — Paul Spiegel, Feb 24 '18 at 06:46
thank you Paul! I knew that problem was with string length, I increased the size of `group_concat_max_len` and now it works) please, write your comment as answer and add some explanations, I will select it — devnull Ψ, Feb 24 '18 at 09:01

devnull Ψ · Accepted Answer · 2019-04-15T14:18:26.013

4

The problem was because of limited string length of GROUP_CONCAT function, which is 1024, thanks to @Paul Spiegel who mentioned this in comments sections. the length can be easily increased, this post explains it clearly

edited Apr 15 '19 at 14:18

answered Feb 24 '18 at 20:12

devnull Ψ

3,779
7
26
43

mysql doesnt return proper json

1 Answers1

Linked