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.

Hi Mr. PRIVATE,

Thank you for your feature request.

Can you just give us a small test case that will display this behaviour. If we can reproduce it, then we will verify this feature request.

Thanks in advance.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

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.

I agree, but due to the existence of the good workaround, it is not a high priority request.

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.

Your note is copied to our internal bugs database.

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

Thank you.

You can include TRIM() function in the proper places ......

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.