MySQL Bugs: #98135: JSON to string includes extraneous spaces...

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:	None
Category:	MySQL Server: JSON	Severity:	S4 (Feature request)
Version:	8	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When MySQL converts JSON to a string it apparently gives no control over the default formatting.

This has two immediate and very obvious effects:

1. The default output includes whitespace which is unnecessary and which may reduce performance.
2. The inclusion of whitespace in the output significantly increases the proportion of cases in which serialized JSON strings from MySQL for the same data will differ from serialised strings from other sources.

Most sources will serialize JSON by default as efficiently as possible so will not include whitespace. Those with specific peculiarities tend to take flags to

There are many cases where two implementations cannot be relied upon to serialize structures to identical strings, for example, object key order, large string handling, escaping of non-ascii, etc. Generally however there are cases that are usually safe, for example:

For [0, 1, 2, 3] virtually all common and basic platform implementations can be relied upon to produce [0,1,2,3] where as MySQL will product [0, 1, 2, 3]. Some deduplication mechanisms may want to rely on traditionally safes cases but would not be able to.

Efficiency speaks for itself, for the ability to match identical documents based on serialized form, removing spaces would not be intended to exhaustively solve that problem.

How to repeat:
Any SQL that causes JSON to be converted to string.

Suggested fix:
Add options to control the format of JSON output, specifically to not include any excessive whitespace.

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.

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.

MySQL Verification Team: could you share the "good workaround" mentioned in Your comment?

Thank you.

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.

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.

I not not see TRIM() as a possible workaround. We are talking about an output of whole JSON object, not a single value.

Hi,

You can try using REPLACE() function .....

Anyway, this is a verified feature request.

REPLACE could work for simpler test cases like the one described in the ticket, but not for complex JSON objects.

Thank you.

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.