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

Answer 1

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