Description:
When inserting or selecting JSON formatted strings containing JSON arrays in a TEXT or MEDIUMTEXT column, MySQL automatically parses and modifies the stored format. This causes inconsistency between the stored data and the retrieved data.
Expected behavior:
SELECT '{"remark":"[\"1\",\"2\",\"3\"]"}';
Should return:
{"remark":"[\"1\",\"2\",\"3\"]"}
Actual behavior:
{"remark":["1","2","3"]}
The original JSON structure is altered. The value of remark is stored as a JSON string but is retrieved as a JSON array. This does not happen when the JSON string contains a JSON object ({"a":"b"}), only when it contains a JSON array (["1","2","3"]).
This is problematic because:
The database returns data in a different format than stored.
MySQL treats JSON arrays and objects inconsistently.
The parsed output is not valid JSON anymore according to JSON specification (RFC 8259).
How to repeat:
Reproducible Test Case
SELECT '{"remark":"[\"1\",\"2\",\"3\"]"}';
Expected:
{"remark":"[\"1\",\"2\",\"3\"]"}
Actual:
{"remark":["1","2","3"]}
Suggested Fix
MySQL should not automatically parse JSON strings unless explicitly requested by the user.
JSON strings should always be treated as plain text unless using JSON_UNQUOTE().
Consistency should be maintained between JSON objects {} and JSON arrays [].
Suggested fix:
- The issue is not that MySQL is parsing JSON, but that MySQL formats JSON in a way that makes it invalid.
- A JSON array stored as a string should remain a string when queried, not be converted into an array that breaks JSON validity.
- This behavior is inconsistent because it happens only with JSON arrays, but not with JSON objects.
- Fixing this behavior would prevent data corruption and ensure consistency in JSON parsing.
Description: When inserting or selecting JSON formatted strings containing JSON arrays in a TEXT or MEDIUMTEXT column, MySQL automatically parses and modifies the stored format. This causes inconsistency between the stored data and the retrieved data. Expected behavior: SELECT '{"remark":"[\"1\",\"2\",\"3\"]"}'; Should return: {"remark":"[\"1\",\"2\",\"3\"]"} Actual behavior: {"remark":["1","2","3"]} The original JSON structure is altered. The value of remark is stored as a JSON string but is retrieved as a JSON array. This does not happen when the JSON string contains a JSON object ({"a":"b"}), only when it contains a JSON array (["1","2","3"]). This is problematic because: The database returns data in a different format than stored. MySQL treats JSON arrays and objects inconsistently. The parsed output is not valid JSON anymore according to JSON specification (RFC 8259). How to repeat: Reproducible Test Case SELECT '{"remark":"[\"1\",\"2\",\"3\"]"}'; Expected: {"remark":"[\"1\",\"2\",\"3\"]"} Actual: {"remark":["1","2","3"]} Suggested Fix MySQL should not automatically parse JSON strings unless explicitly requested by the user. JSON strings should always be treated as plain text unless using JSON_UNQUOTE(). Consistency should be maintained between JSON objects {} and JSON arrays []. Suggested fix: - The issue is not that MySQL is parsing JSON, but that MySQL formats JSON in a way that makes it invalid. - A JSON array stored as a string should remain a string when queried, not be converted into an array that breaks JSON validity. - This behavior is inconsistent because it happens only with JSON arrays, but not with JSON objects. - Fixing this behavior would prevent data corruption and ensure consistency in JSON parsing.