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.