MySQL Bugs: #98043: JSON array only supports in?

Bug #98043	JSON array only supports in?
Submitted:	20 Dec 2019 20:36	Modified:	4 Feb 2020 23:20
Reporter:	Joey PRIVATE	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
The only thing I can get to work is CAST(col AS UNSIGNED ARRAY). It states AS [type] ARRAY but putting in normal column type definitions gives an error.

How to repeat:
ALTER TABLE table ADD INDEX name((CAST col AS ARRAY));

Suggested fix:
Support standard column types.

The problem seems to be the types it takes are limited and it's hard to divine this easily from the manual.

Some types cause syntax errors, others cause it to state the type isn't supported.

It can't take types such as varchar. The impact if any of this should probably be documented.

Hello Mr. PRIVATE,

Thank you for your bug report.

I must admit I am quite puzzled by your report. First of all, I do not know whether you report a bug or are you asking for a feature request.

Second, I do not see what MySQL version are you referring to ??

Third, can you be more precise with the category where your report belongs to.

Last, but not least, can you send us a full test case that will demonstrate to us what is it that is a bug in our server.

Thank you very much, in advance.

This is for MySQL 8, latest and is likely a case of either missing documentation or partially completed implementation of the feature.

The feature concerned is being able to index multiple values from a JSON array stored inside a JSON column.

When creating such an index, it requires a CAST to specify the type, however not all types are supported and it's not necessarily clear in the documentation.

https://dev.mysql.com/doc/refman/8.0/en/create-index.html#create-index-multi-valued

https://dev.mysql.com/doc/refman/8.0/en/create-index.html

https://dev.mysql.com/doc/refman/8.0/en/cast-functions.html#function_cast

From example:

> INDEX zips( (CAST(custinfo->'$.zip' AS UNSIGNED ARRAY)) )

> (CAST json_path AS type ARRAY)

It's not entirely clear from the multi-value index docs but CAST does not support types such as VARCHAR.

Indexes can otherwise be applied to VARCHAR columns. It's not clear in the documentation if using CHAR instead of VARCHAR would have any impact especially on storage size.

Particularly because apart from padding in some cases, the only difference between CHAR and VARCHAR is storage size.

Hi Mr. PRIVATE,

I have fully analysed what you were writing about and I think that our documentation is not totally clear on this matter.

Verified as a bug in our documentation.

The documentation for multi-valued indexes says, "All type specifiers supported by CAST()can be used except for BINARY and JSON." The documentation for CAST() refers the reader to the documentation for CONVERT(), which does not list VARCHAR as a supported type.

Thank you, Jon .....