Bug #80545 JSON field should be allow INDEX by specifying table_flag
Submitted: 28 Feb 2016 3:09 Modified: 22 Mar 2018 5:44
Reporter: ナオヤ ムラカミ 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

[28 Feb 2016 3:09] ナオヤ ムラカミ 
Description:
Hi,

I am MySQL 5.7 user and patch developer of Mroonga storage engine that fulltext search engine.

Currently, MySQL doesn't allow set any key type to JSON type field.

https://github.com/mysql/mysql-server/blob/5.7/sql/sql_table.cc#L4149-L4154

Using inverted index algorithm in storage engine side, it can be searched against any key or element in JSON.

How to repeat:
mysql> CREATE TABLE records (
    ->   body JSON,
    ->   KEY (body)
    -> ) DEFAULT CHARSET=utf8mb4;
ERROR 3152 (42000): JSON column 'body' cannot be used in key specification.

Suggested fix:
It would be great if JSON type field allow set index specifying table_flag such as "HA_CAN_INDEX_JSON".
[22 Mar 2018 5:44] MySQL Verification Team 
Hello!

Thank you for the feature request!

Thanks,
Umesh
[22 Mar 2018 5:45] MySQL Verification Team 
Some of the documented workarounds - https://dev.mysql.com/doc/refman/5.7/en/create-table-secondary-indexes.html#json-column-in...

https://mysqlserverteam.com/indexing-json-documents-via-virtual-columns/