MySQL Bugs: #80349: MySQL 5.7 JSON: improve documentation and possible improvements

Bug #80349	MySQL 5.7 JSON: improve documentation and possible improvements
Submitted:	12 Feb 2016 7:53	Modified:	22 Mar 2018 5:49
Reporter:	Simon Mudd (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: JSON	Severity:	S4 (Feature request)
Version:	5.7	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
MySQL 5.7 comes with new functionality JSON which is likely to be of interest to some.  After some presentations at some user groups a couple of small things came out which might be worth documenting more clearly.

How to repeat:
See above.

Suggested fix:
1. If the JSON on disk binary format is not documented. Please do that.

2. Is it possible to extract the binary stored format from a JSON column without conversion? 

While this may not be needed frequently it might be convenient sometimes and would not be possible without (1) above.  I'd expect a SELECT CAST( json_column AS VARBINARY) or similar to allow me to do that. Is that possible?

3. It seems there may be some padding added to the binary stored JSON data intended for later optimisations. For systems where a lot of data is stored this padding may be unnecessary and the extra space required for it may be unhelpful. Documenting this in (1) would help but if potentially this can optionally be avoided having some configuration options for the column to adjust this behaviour seems useful.

I think this is mainly a documentation issue but adding that means that the storage format is transparent and in the few cases where optimisations may be needed this might be possible.

The format is described in the worklog here:
https://dev.mysql.com/worklog/task/?id=8132

On the second point, I'm not sure:

mysql [localhost] {msandbox} (test) > show create table aa1\G
*************************** 1. row ***************************
       Table: aa1
Create Table: CREATE TABLE `aa1` (
  `a` int(11) NOT NULL,
  `b` json DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql [localhost] {msandbox} (test) > insert into aa1 values (1, json_array(1,2,3));
Query OK, 1 row affected (0.02 sec)

mysql [localhost] {msandbox} (test) > warnings;
Show warnings enabled.
mysql [localhost] {msandbox} (test) > select b+0 from aa1;
+------+
| b+0  |
+------+
|    0 |
+------+
1 row in set, 1 warning (0.00 sec)

Warning (Code 3156): Invalid JSON value for CAST to DOUBLE: '[1, 2, 3]' from b at row 1
mysql [localhost] {msandbox} (test) > select cast(b as binary) from aa1;
+-------------------+
| cast(b as binary) |
+-------------------+
| [1, 2, 3]         |
+-------------------+
1 row in set (0.01 sec)

Hello Simon,

Thank you for the report and feature request!

Thanks,
Umesh