mysql没有返回正确的json [英] mysql doesnt return proper json

问题描述

我使用mysql 5.7

I use mysql 5.7

我想将联接表的结果连接到单列中.我使用包裹在GROUP_CONCAT函数中的JSON_OBJECT函数.这是查询

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

列ingredients包含来自联接表的数据.数据已正确检索，但问题是ingredients列未转换为正确的JSON. 如您所见，最后有点切".

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.

我也尝试过JSON_ARRAY，但是现在它不能转换为正确的数组.它看起来像字符串太大，不适合列.有办法解决这个问题吗?

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?

更新

问题必须出在不适合列的字符串大小中.我尝试从联接表中选择较少的列，并且它可以正常工作.也许有一种方法可以在选择时将我的json列即时"转换为文本?我试图将列转换为类似

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) 

但是它给出了语法错误

推荐答案

问题是由于GROUP_CONCAT函数的字符串长度有限，即1024，这要归功于@Paul Spiegel在评论部分中提到的问题.长度很容易增加，这则帖子清楚地说明了

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

这篇关于mysql没有返回正确的json的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！