json - 如何在mysql中为json创建索引？

Question

我有更新版本的mysql作为

选择版本（）

返回 10.4.11-MariaDB

我能够创建 json 类型表但无法在 mysql 中创建索引

CREATE TABLE `players` (  
   `id` INT UNSIGNED NOT NULL,
   `player_and_games` JSON NOT NULL,
   `names_virtual` VARCHAR(20) GENERATED ALWAYS AS (`player_and_games` ->> '$.name') NOT NULL, 
   PRIMARY KEY (`id`)
);

我运行这个命令它的返回

执行了 1 个查询，0 个成功，1 个错误，0 个警告

询问：

CREATE TABLE `players` (
  `id` INT UNSIGNED NOT NULL,
  `player_and_games` JSON NOT NULL,
  `names_virtual` VARCHAR(20) GENERATED ALW...

Error Code: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '>> '$.name') NOT NULL, 
   PRIMARY KEY (`id`)
)' at line 4

Execution Time : 0 sec
Transfer Time  : 0 sec
Total Time     : 0 sec

我还使用了 mysql 官方文档，它也返回了同样的问题。

标签： jsonmariadb

Answer 1

尝试使用json_value(). 并且不能NOT NULL明显声明生成的列。

CREATE TABLE `players` (  
   `id` INT UNSIGNED NOT NULL,
   `player_and_games` JSON NOT NULL,
   `names_virtual` VARCHAR(20) GENERATED ALWAYS AS (json_value(`player_and_games`, '$.name')), 
   PRIMARY KEY (`id`)
);