Bug #97589 JSON fields return altered data
Submitted: 12 Nov 2019 0:49 Modified: 15 Nov 2019 16:33
Reporter: Andrew Brown Email Updates:
Status: Closed Impact on me:
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:8.0.18, 5.7.28 OS:Any
Assigned to: Jon Stephens CPU Architecture:Any

[12 Nov 2019 0:49] Andrew Brown
I'm not sure if this qualifies as a bug per se, but the behavior seems inconsistent.

With a JSON field, the value returned from the query will always contain a space between the elements, even if the data was entered without a space.

If there is no "fix" for this, I feel like it should be documented.

How to repeat:
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `emails` json NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `test` (`id`, `emails`)
VALUES (1, '[\"ashtyn.jakubowski@yahoo.com\",\"heaney.claire@hotmail.com\",\"preston52@hotmail.com\"]')

SELECT `emails` FROM `test` WHERE `id` = 1

The query will return 

["ashtyn.jakubowski@yahoo.com", "heaney.claire@hotmail.com", "preston52@hotmail.com"]

So even though we entered the data without spaces between the elements, it returned with the spaces.

Suggested fix:
It would be great if the data was returned exactly as it was entered, but I'm not sure how that affects the internal JSON handling of MySQL.
[12 Nov 2019 2:48] Tsubasa Tanaka
I found this at JSON chapter in document.

> MySQL also discards extra whitespace between keys, values, or elements in the original JSON document. To make lookups more efficient, it also sorts the keys of a JSON object


From Japan MySQL User Group.
[12 Nov 2019 3:29] Andrew Brown
I saw that as well. Which seems like the opposite of what it's doing. So I figured that was for its internal handling.
[12 Nov 2019 6:12] MySQL Verification Team
Hello Andrew Brown,

Thank you for the report.

[14 Nov 2019 17:38] Jon Stephens
Waiting on Dev to confirm that this behaviour is as intended (or not).
[15 Nov 2019 13:21] Knut Anders Hatlen
Posted by developer:
Yes, this is intentional. The spaces between the elements are not stored internally. However, when converting the JSON value to a string for presentation, a blank is added between each element to make the value easier to read. A blank is also added between after the colon that separates key names from values in JSON objects.
[15 Nov 2019 13:35] Jon Stephens
Hi Andrew,

Our Development staff have verified that this is intended/expected. We'll update the documentation to reflect this soon.


[15 Nov 2019 14:27] Jon Stephens
Fixed in mysqldoc rev 64194.
[15 Nov 2019 14:27] Jon Stephens
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly.
[15 Nov 2019 16:33] Andrew Brown
Thanks for the quick response on this guys, and for making the documentation updates.

I'm still a little torn on if this is the appropriate behavior or not, but at least for now I can handle this discrepancy on the server code.