An example of creating Index query for MySQL5.7 JSON Type column
Create a table of type json test:
Mysql > CREATE TABLE test (data JSON)
Query OK, 0 rows affected (0.47 sec)
Mysql > insert into test values ('{"name": "abc", "sex": "nan", "area": ["1", "2"]}')
Query OK, 1 row affected (0.39 sec)
Mysql > insert into test values ('{"name": "abc", "sex": "nan", "area": ["2", "3"]}')
Query OK, 1 row affected (0.39 sec)
Mysql > insert into test values ('{"name": "abc", "sex": "nan", "area": ["3", "4"]}')
Query OK, 1 row affected (0.39 sec)
Mysql > select json_type (data) from test
+-+
| | json_type (data) |
+-+
| | OBJECT |
+-+
1 row in set (0.15 sec)
Mysql > select * from test
+-- +
| | data |
+-- +
| | {"sex": "nan", "area": ["1", "2"], "name": "abc"} |
+-- +
1 row in set (0.10 sec)
Mysql > select json_extract (data,'$.name') from test
+-+
| | json_extract (data,'$.name') |
+-+
| | "abc" |
+-+
1 row in set (0.00 sec)
Mysql > select json_extract (data,'$.sex') from test
+-+
| | json_extract (data,'$.sex') |
+-+
| | "nan" |
+-+
1 row in set (0.00 sec)
Mysql > select json_extract (data,'$.area') from test
+-+
| | json_extract (data,'$.area') |
+-+
| | ["1", "2"] |
+-+
1 row in set (0.00 sec)
On the data column, create a virtual column for "area"
Mysql > ALTER TABLE test ADD data_idx varchar 128GENERATED ALWAYS AS (json_extract (data,'$.area')) VIRTUAL
Query OK, 0 rows affected (0.93 sec)
Records: 0 Duplicates: 0 Warnings: 0
If you want to retrieve on the JSON column, you need to create a virtual column on the retrieved key, and then create an index on the virtual column.
Mysql > alter table test add index idx_data (data_idx)
Query OK, 0 rows affected (0.67 sec)
Records: 0 Duplicates: 0 Warnings: 0
The where condition needs to be retrieved using virtual columns, and the execution plan is as follows:
Mysql > explain select * from test where data_idx=' ["3", "4"]'
+-- +
| | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+-- +
| | 1 | SIMPLE | test | NULL | ref | idx_data | idx_data | 387 | const | 1 | 100.00 | NULL |
+-- +
1 row in set, 1 warning (0.04 sec)
It is found that the index is gone.
