Mysql Json

mysql> select * from json_test1 where JSON_SEARCH(person_desc, 'one','value2')  is not null;

+----+-----------------------------------------------------------------------------+

| id | person_desc                                                                 |

+----+-----------------------------------------------------------------------------+

|  1 | {"type1": ["value1", "value2"], "type2": ["value11", "value21", "value31"]} |

+----+-----------------------------------------------------------------------------+

1 row in set (0.00 sec)

mysql> explain select * from json_test1 where JSON_SEARCH(person_desc, 'one','value2')  is not null;

+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+

| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |

+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+

|  1 | SIMPLE      | json_test1 | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | Using where |

+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+

1 row in set, 1 warning (0.00 sec)

mysql> alter table json_test1 add col1 varchar(100) generated always as (person_desc->'$.type1[1]');

Query OK, 0 rows affected (0.01 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table json_test1 add index idx_col1(col1);

Query OK, 0 rows affected (0.01 sec)

mysql> desc json_test1;

+-------------+--------------+------+-----+---------+-------------------+

| Field       | Type         | Null | Key | Default | Extra             |

+-------------+--------------+------+-----+---------+-------------------+

| id          | int(11)      | NO   | PRI | NULL    | auto_increment    |

| person_desc | json         | YES  |     | NULL    |                   |

| col1        | varchar(100) | YES  | MUL | NULL    | VIRTUAL GENERATED |

+-------------+--------------+------+-----+---------+-------------------+

根据虚拟列查询，因为建立的索引，所以不是全表扫描

mysql> explain select * from json_test1 where col1='value2';

+----+-------------+------------+------------+------+---------------+----------+---------+-------+------+----------+-------+

| id | select_type | table      | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |

+----+-------------+------------+------------+------+---------------+----------+---------+-------+------+----------+-------+

|  1 | SIMPLE      | json_test1 | NULL       | ref  | idx_col1      | idx_col1 | 103     | const |    1 |   100.00 | NULL  |

+----+-------------+------------+------------+------+---------------+----------+---------+-------+------+----------+-------+

1 row in set, 1 warning (0.00 sec)

mysql> alter table json_test1 add col2  json generated always as (person_desc->'$.type1');

Query OK, 0 rows affected (0.02 sec)

mysql> alter table json_test1 add index idx_col2(col2);

ERROR 3152 (42000): JSON column 'col2' cannot be used in key specification.