Bug #98135 | JSON to string includes extraneous spaces... | ||
---|---|---|---|
Submitted: | 6 Jan 2020 14:32 | Modified: | 10 May 2020 8:00 |
Reporter: | Joey PRIVATE | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: JSON | Severity: | S4 (Feature request) |
Version: | 8 | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[6 Jan 2020 14:32]
Joey PRIVATE
[10 May 2020 8:00]
MySQL Verification Team
No need for a specific testcase. Anything will do: -- ----- create table t(a json); set @a:=concat('[',repeat('1,',100),'1]'); insert into t values(@a); select a from t; -- ----- Output is: [1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1] A third of the output is wasted on whitespaces. This would be more compact: [1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1] Seems like reporter wants a function, opposite of JSON_PRETTY that outputs the latter compact format.
[10 Feb 2022 21:19]
Marcin Gil
This causes serious issues with compatibility with json_encode in PHP. After data is saved and read, the string is altered with MySQL format (added spaces). Some libs like ORMs will think that the field was modified.
[16 May 2023 19:32]
Marcin Gil
MySQL Verification Team: could you share the "good workaround" mentioned in Your comment? Thank you.
[17 May 2023 13:22]
MySQL Verification Team
Obviously TRIM() won't work. One might be tempted to use REPLACE() but it will only work on datasets that don't contain legitimate spaces: mysql> select replace(a,' ','') from t; +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | replace(a,' ','') | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | [1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1] | | [1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1] | | [1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1] | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 3 rows in set (0.00 sec) Hence, there is no workaround.
[27 Jul 2023 12:24]
Marcin Gil
It's hard to say who should adjust - PHP or MySQL, but the fact is that json output is different at the moment and two technologies which are often used together do not have a common json output format. It's not possible to bend one or another to get exactly the same output. For example one cannot use json_encode in PHP to produce exactly same format as MYSQL CAST(string as JSON). Taking example of an ORM library for PHP, it causes a case when json value is treated as modified, even though it was only decoded and encoded again.
[27 Jul 2023 12:27]
Marcin Gil
I not not see TRIM() as a possible workaround. We are talking about an output of whole JSON object, not a single value.
[27 Jul 2023 12:49]
MySQL Verification Team
Hi, You can try using REPLACE() function ..... Anyway, this is a verified feature request.
[27 Jul 2023 13:21]
Marcin Gil
REPLACE could work for simpler test cases like the one described in the ticket, but not for complex JSON objects. Thank you.
[5 May 9:16]
Lauri Elevant
Leaving aside compatibility with other implementations (such as PHP above), both [1,2,3] and [1, 2, 3] are valid JSON and to spec. But one is obviously better and more efficient. It should be an easy way to improve MySQL without introducing any BC issues.