JSON_TABLE(
    expr,
    path COLUMNS (column_list)
)   [AS] alias

set @jsoninfo='[{"name":"Alice"},{"name":"Bob"},{"name":"Cindy"}]';
SELECT * from JSON_TABLE(@jsoninfo,
                         "$[*]" COLUMNS( 
                             rowid FOR ORDINALITY,
                             name VARCHAR(100) PATH "$.name")) as t;
查询结果：
+-------+-------+
| rowid | name  |
+-------+-------+
|     1 | Alice |
|     2 | Bob   |
|     3 | Cindy |
+-------+-------+

从数据库中进行取值：

原始 json_data 列数据：
>> [{"name":"Alice"},{"name":"Bob"},{"name":"Cindy"}]
SELECT t.* from json_demo,JSON_TABLE(json_data,"$[*]" COLUMNS( rowid FOR ORDINALITY,name VARCHAR(100) PATH "$.name")) as t;
查询结果：
+-------+-------+
| rowid | name  |
+-------+-------+
|     1 | Alice |
|     2 | Bob   |
|     3 | Cindy |
+-------+-------+

原始 json_data 列数据（在表中 id = 2）：
>> [{"x":2,"y":"8"},{"x":"3","y":"7"},{"x":"4","y":6}]
SELECT t.* from json_demo a,
JSON_TABLE(json_data,"$[*]" COLUMNS( 
    rowid FOR ORDINALITY,xval VARCHAR(100) PATH "$.x",
    yval VARCHAR(100) PATH "$.y")) as t
where a.id=2;
查询结果：
+-------+------+------+
| rowid | xval | yval |
+-------+------+------+
|     1 | 2    | 8    |
|     2 | 3    | 7    |
|     3 | 4    | 6    |
+-------+------+------+

CREATE TABLE `employee` (
  `id` int NOT NULL AUTO_INCREMENT,
  `json_data` mediumtext NULL,
  `name` varchar(20) GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(`json_data`,'$.name'))) VIRTUAL NULL,
  PRIMARY KEY (`id`),
  KEY `name_index` (`name`)
) ENGINE=InnoDB

或者使用内联路径运算符 ->> ，如下所示：

CREATE TABLE `employee` (
  `id` int NOT NULL AUTO_INCREMENT,
  `json_data` mediumtext NULL,
  `name` varchar(20) GENERATED ALWAYS AS (json_data->>'$.name') VIRTUAL NULL,
  PRIMARY KEY (`id`),
  KEY `name_index` (`name`)
) ENGINE=InnoDB

其中，name 列即为虚拟字段，它是根据 json_data 列的 Json 数据，自动生成的。

对表中插入数据：

// Json 中包含 name 
INSERT INTO employee (`json_data`) VALUES ('{"name":"Alice","age":25}');
INSERT INTO employee (`json_data`) VALUES ('{"name":"Bob","age":28}');
INSERT INTO employee (`json_data`) VALUES ('{"name":"Cindy","age":24}');
// Json 中不包含 name 
INSERT INTO employee (`json_data`) VALUES ('{"sex":"male","age":24}');
// 插入非 Json 的数据
INSERT INTO employee (`json_data`) VALUES ('test text info');

可以发现，前面四条数据都添加成功了，第四条由于不包含 name ，所以 name 虚拟列为 null。

但五条数据报错，因为它不是 Json 格式：

Invalid Json text in argument 1 to function json_extract: "Invalid value." at position 1

使用 JSON_SET 更新某条 Json 数据，可以发现虚拟列也同步进行了更新：

原始 json_data 列数据：
>> {"name":"Bob","age":28}
update employee set json_data=JSON_SET(json_data, '$.name', 'Bassam') where id=2;
更新后数据：
+----+-------------------------------+--------+
| id | json_data                     | name   |
+----+-------------------------------+--------+
|  2 | {"age": 28, "name": "Bassam"} | Bassam |
+----+-------------------------------+--------+

explain SELECT name FROM employee where name='Bob';

会用到 name 虚拟列的 name_index 索引。

通过 json_data->'$.name' 查询，也会用到 虚拟列的 name_index 索引。

explain SELECT name FROM employee where json_data->'$.name'='Bob';

建表示例如下：

CREATE TABLE `employee` (
  `id` int NOT NULL AUTO_INCREMENT,
  `json_data` mediumtext,
  PRIMARY KEY (`id`),
  KEY `json_data_name_index` ((cast(`json_data`->>'$.name' as char(255))))
) ENGINE=InnoDB

新增索引格式：

ALTER TABLE table_name ADD KEY ( (CAST(column_name -> '$' AS UNSIGNED ARRAY)) );

新增语句如前所述。

查看 explain 执行计划：

explain SELECT * FROM `test`.`employee` where json_data->>'$.name'='Bob';

可以发现并没有用到所创建的索引。

这是由于前面表定义中的索引表达式 WHERE 与查询中的子句表达式之间存在排序规则不匹配，参考。

explain SELECT * FROM `test`.`employee` where CAST(json_data->>'$.name'AS CHAR(25))='Bob';

explain FORMAT=TREE SELECT * FROM `test`.`employee` where CAST(json_data->>'$.name'AS CHAR(25))='Bob';
>> -> Index lookup on employee using json_data_name_index (cast(json_unquote(json_extract(json_data,_utf8mb3'$.name')) as char(25) charset utf8)='Bob')  (cost=0.35 rows=1)