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.

The question closely follows the pattern described in How do I generate nested json objects using mysql native json functions?

Thanks for any ideas!

Cheers F

Answer 1

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