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:
Category:MySQL Server: JSON Severity:S4 (Feature request)
Version:5.7 OS:Any
Assigned to: CPU Architecture:Any
Triage: Needs Triage: D5 (Feature request)

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

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.


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] Umesh Shastry

Thank you for the feature request!

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