Nested json object from mysql: error 'missing a closing quotation mark in string' even with static values

Question

I'm attempting to generate a nested, hierarchical JSON from MySQL.

I keep getting the error message:

Error Code: 3141. Invalid JSON text in argument 1 to function cast_as_json: "Missing a closing quotation mark in string." at position 1026.

I have attempted the following and always got the same error:

enclosed the problematic column name in `...`
enclosed the problematic column in JSON_QUOTE(...)
replaced the problematic column by a static string which includes whitespace characters
enclosed the problematic column in CAST( ... ) as JSON

The following tests made the problem disappear but they're obviously not an option:

replaced the problematic column by a static string which DOES NOT include whitespace characters => problem does NOT occur
replace the problematic column by a different column which does NOT contain whitespaces => problem does NOT occur

I've googled for hours and really don't know any further.

Thanks for any ideas!

Cheers F

Husein Nashr · Accepted Answer · 2020-04-14 07:49:50Z

8

Are you using GROUP_CONCAT? if so, there's a chance that it cuts your string out because it's too long resulting in invalid JSON string.

Try changing the group_concat_max_len for ex 100000 characters length:

SET SESSION group_concat_max_len = 100000;

I know it's a bit too late :)

answered Apr 14, 2020 at 7:36

Husein Nashr

813 silver badges5 bronze badges

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

1 Answer 1