MySQL JSON Data Type

序言

MySQL 在 5.7.8 版本中首次引入 JSON 数据类型。这使得我们可以更好地处理和操作 JSON 数据，包括存储、查询和索引 JSON 数据。在此之前，要处理 JSON 数据，通常需要将其存储为文本，并使用字符串函数进行操作。程序中我们也无法直接使用从数据库获取的 JSON 数据，需要进一步转化，十分不方便。

JSON 数据类型相对于将 JSON 格式字符串存储在字符串列中具有以下优势：

• 在 JSON 列中存储的 JSON 文档会自动进行验证，无效的文档会引发错误。
• 经过优化的存储格式。存储在 JSON 列中的 JSON 文档被转换为内部格式，允许快速读取文档元素。当服务器需要读取存储在二进制格式中的 JSON 值时，无需从文本表示中解析该值。二进制格式被构造成允许服务器直接通过键或数组索引查找子对象或嵌套值，而不需要读取文档中它们之前或之后的所有值。

随着 MySQL 的版本更新，JSON 数据类型的功能和性能也得到了改进。接下来我们就以 MySQL 8.0.26 版本中 InnoDB 存储引擎为例探讨一下 JSON 格式的数据类型。

创建JSON值

在 MySQL 中，JSON 值将被写为字符串。MySQL解析任何在需要 JSON 值的上下文中使用的字符串，如果它不是有效的 JSON，则会产生错误。

这些上下文包括将值插入具有 JSON 数据类型的列中，并将参数传递给期望 JSON 值的函数（通常在MySQL JSON 函数的文档中显示为 json_doc 或 json_val ），如下例所示：

• 如果值是有效的 JSON 值，则尝试将值插入 JSON 列会成功，否则，便会失败：

  create table json_test
  (
      id        int auto_increment comment '主键'
          primary key,
      json_info json null comment 'json格式信息'
  );
  
  insert into json_test(json_info) values ('{"name": "小明", "age": 18, "sex" : 1}');
  1 row affected in 52 ms
  
  insert into json_test(json_info) values ('{"name": "小红", "age": 20, "sex"}');
  Data truncation: Invalid JSON text: "Missing a colon after a name of object member." at position 35 in value for column 'json_test.json_info'.

• JSON_TYPE() 函数需要一个 JSON 参数，并尝试将其解析为 JSON 值。如果有效，它会返回值的 JSON 类型，否则会产生错误：

  mysql> SELECT JSON_TYPE('["吃饭", false, 20]');
  +----------------------------------+
  | JSON_TYPE('["吃饭", false, 20]') |
  +----------------------------------+
  | ARRAY                            |
  +----------------------------------+
  
  mysql> SELECT JSON_TYPE('"你好"');              
  +---------------------+
  | JSON_TYPE('"你好"') |
  +---------------------+
  | STRING              |
  +---------------------+
  
  mysql> SELECT JSON_TYPE('你好');   
  ERROR 3141 (22032): Invalid JSON text in argument 1 to function json_type: "Invalid value." at position 0.

手动输入 JSON 格式字符串非常容易出错，MySQL 为我们提供了一些方法作为代替方案：

• JSON_ARRAY() 获取一个（可能为空）值列表，并返回一个包含这些值的 JSON 数组：

  mysql> SELECT JSON_ARRAY('数组', 123, NOW()); 
  +---------------------------------------------+
  | JSON_ARRAY('数组', 123, NOW())              |
  +---------------------------------------------+
  | ["数组", 123, "2023-10-18 11:30:59.000000"] |
  +---------------------------------------------+

• JSON_OBJECT() 获取键值对的列表（可能为空），并返回包含这些键值对的 JSON 对象：

  mysql> SELECT JSON_OBJECT('name', '小红', 'age', 20, 'sex', 0);
  +--------------------------------------------------+
  | JSON_OBJECT('name', '小红', 'age', 20, 'sex', 0) |
  +--------------------------------------------------+
  | {"age": 20, "sex": 0, "name": "小红"}            |
  +--------------------------------------------------+

• JSON_MERGE_PRESERVE() 获取两个或多个 JSON 文档并返回组合结果：

  mysql> SELECT JSON_MERGE_PRESERVE('["你好", true]', '{"name": "李四"}');  
  +-----------------------------------------------------------+
  | JSON_MERGE_PRESERVE('["你好", true]', '{"name": "李四"}') |
  +-----------------------------------------------------------+
  | ["你好", true, {"name": "李四"}]                          |
  +-----------------------------------------------------------+

• JSON 值可以分配给用户定义的变量：

  mysql> SET @j = JSON_OBJECT('name', '老李'); 
  mysql> SELECT @j;
  +------------------+
  | @j               |
  +------------------+
  | {"name": "老李"} |
  +------------------+

  然而，用户定义的变量不能是 JSON 数据类型，因此尽管前面示例中的 @j 看起来像 JSON 值，并且具有与 JSON 值相同的字符集和排序规则，但它没有 JSON 数据类型。相反，JSON_OBJECT() 的结果在分配给变量时会转换为字符串。

  通过转换 JSON 值生成的字符串具有 utf8mb4 字符集和 utf8mb4_bin排序规则；由于 utf8mb4_bin 是二进制排序规则，因此 JSON 值的比较区分大小写:

  mysql> SELECT CHARSET(@j), COLLATION(@j);
  +-------------+---------------+
  | CHARSET(@j) | COLLATION(@j) |
  +-------------+---------------+
  | utf8mb4     | utf8mb4_bin   |
  +-------------+---------------+
  
  mysql> SELECT JSON_ARRAY('x') = JSON_ARRAY('X');
  +-----------------------------------+
  | JSON_ARRAY('x') = JSON_ARRAY('X') |
  +-----------------------------------+
  |                                 0 |
  +-----------------------------------+

有时，我们需要在 JSON 文档中插入引号字符（“ 或 ”）。

例如，我们希望将这行字符串以 key/value 键值对的方式插入到 JSON 文档中。

mascot: The MySQL mascot is a dolphin named "Sakila".

其中一种作为 JSON 对象插入表中的一种方法是使用 MySQL JSON_object() 函数。在这种情况下，必须使用反斜杠对每个引号字符进行转义，如下所示：

mysql> INSERT INTO json_test(json_info) VALUES(JSON_OBJECT("mascot", "Our mascot is a dolphin named \"Sakila\"."));

如果将值作为 JSON 对象文字插入，则此操作的方式与此不同，在这种情况下，必须使用双反斜杠转义序列，如下所示：

mysql> INSERT INTO json_test(json_info) VALUES('{"mascot": "Our mascot is a dolphin named \\"Sakila\\"."}');

使用双反斜杠可以防止 MySQL 执行转义序列处理，而是将字符串文本传递给存储引擎进行处理。以刚才显示的任何一种方式插入 JSON 对象后，通过执行简单的 SELECT 查询，我们可以看到 JSON 列值中存在反斜杠，如下所示：

mysql> SELECT json_info from json_test;
+---------------------------------------------------------+
| json_info                                               |
+---------------------------------------------------------+
| {"mascot": "Our mascot is a dolphin named \"Sakila\"."} |
+---------------------------------------------------------+

我们可以使用列路径操作符 -> 来查找这个使用 mascot 作为关键字的特定句子，如下所示：

mysql> SELECT json_info->"$.mascot" FROM json_test; 
+---------------------------------------------+
| json_info->"$.mascot"                       |
+---------------------------------------------+
| "Our mascot is a dolphin named \"Sakila\"." |
+---------------------------------------------+

这将会保留反斜杠以及周围的引号。要使用吉祥物作为键显示所需的值，但不包括周围的引号或任何转义符，可以使用内联路径运算符 ->>，如下所示：

mysql> SELECT json_info->>"$.mascot" FROM json_test; 
+-----------------------------------------+
| json_info->>"$.mascot"                  |
+-----------------------------------------+
| Our mascot is a dolphin named "Sakila". |
+-----------------------------------------+

如果启用了 NO_BACKSLASH_ESCAPES 服务器 SQL 模式，则上一个示例将无法正常工作。如果设置了此模式，则可以使用单个反斜杠而不是双反斜杠来插入 JSON 对象文字，并保留反斜杠。如果在执行插入时使用 JSON_OBJECT() 函数，并且设置了此模式，则必须交替使用单引号和双引号，如下所示：

mysql> INSERT INTO json_test(json_info) VALUES (JSON_OBJECT('mascot', 'Our mascot is a dolphin named "Sakila".'));

JSON 值的规范化、合并和自动包装

规范化

当一个字符串被解析为一个有效的 JSON 文档时，同时也会被规范化。这意味着，从左到右读取时，与文档后面重复的键会被丢弃。所以下面的 JSON_OBJECT() 调用产生的对象值只包含第二个 key1 元素。

RFC 7159 建议采用这种 “最后一个重复键获胜 “的行为，大多数 JavaScript 解析器都采用了这种行为。

mysql> SELECT JSON_OBJECT('key1', 1, 'key2', 'abc', 'key1', 'def');
+------------------------------------------------------+
| JSON_OBJECT('key1', 1, 'key2', 'abc', 'key1', 'def') |
+------------------------------------------------------+
| {"key1": "def", "key2": "abc"}                       |
+------------------------------------------------------+

-- 这对于在 `JSON` 列中插入值同样有效。
mysql> INSERT INTO json_test(json_info) VALUES ('{"x": 17, "x": "red"}'), ('{"x": 17, "x": "red", "x": [3, 5, 7]}');
Query OK, 2 rows affected (0.04 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select json_info from json_test;
+---------------------------------------------------------+
| json_info                                               |
+---------------------------------------------------------+
| {"x": "red"}                                            |
| {"x": [3, 5, 7]}                                        |
+---------------------------------------------------------+

注意：在 MySQL 8.0.3 之前的版本中，重复键的处理策略与上面恰好相反，也就是执行 first duplicate key wins 的规范化处理。

mysql> SELECT JSON_OBJECT('key1', 1, 'key2', 'abc', 'key1', 'def');
+------------------------------------------------------+
| JSON_OBJECT('key1', 1, 'key2', 'abc', 'key1', 'def') |
+------------------------------------------------------+
| {"key1": 1, "key2": "abc"}                           |
+------------------------------------------------------+

-- 这对于在 `JSON` 列中插入值同样有效。
mysql> INSERT INTO json_test(json_info) VALUES ('{"x": 17, "x": "red"}'), ('{"x": 17, "x": "red", "x": [3, 5, 7]}');
Query OK, 2 rows affected (0.04 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select json_info from json_test;
+---------------------------------------------------------+
| json_info                                               |
+---------------------------------------------------------+
| {"x": 17}                                               |
| {"x": 17}                                               |
+---------------------------------------------------------+

MySQL 还会丢弃原始 JSON 文档中键、值或元素之间的多余空白，为了提高可读性会在显示时，在每个逗号（,）或冒号（:）后保留（或在必要时插入）一个空格。我们日常开发时在符号和逗号后面保留一个空格也是为了提高可读性。

对于使用 MySQL 函数生成的 JSON 值总是返回规范化的处理。

为了提高查找效率，MySQL 还会对 JSON 对象的键进行排序。需要注意的是，这种排序的结果可能会发生变化，而且不能保证在不同版本中保持一致。

JSON值合并

MySQL 8.0.3（及更高版本）支持两种合并算法，由函数 JSON_MERGE_PRESERVE() 和 JSON_MERGE_PATCH() 实现

它们在处理重复键的方式上有所不同：

• JSON_MERGE_PRESERVE() 会保留重复键的值。
• JSON_MERGE_PATCH() 会丢弃除最后一个值以外的所有重复值。

JSON_MERGE_PRESERVE() 与 MySQL 以前版本中的 JSON_MERGE() 函数相同（MySQL 8.0.3 中重新命名）。在 MySQL 8.0 中，JSON_MERGE() 作为 JSON_MERGE_PRESERVE() 的别名仍受支持，但已被弃用，并可能在未来的版本中删除。

合并数组

在由多个数组组合的上下文中，这些数组会合并为一个数组。

• JSON_MERGE_PRESERVE() 通过将后面命名的数组连接到第一个数组的末尾来实现这一功能。也就是从左往右，将多个数组中的元素首尾相连，最终合并为一个数组。

• JSON_MERGE_PATCH() 将每个数组视为由单个元素组成的数组（因此每个数组的索引为 0），然后应用 last duplicate key wins 逻辑，只选择最后一个数组。

mysql> SELECT 
    -> JSON_MERGE_PRESERVE('[1, 2]', '["a", "b", "c"]', '[true, false]') AS Preserve,
    -> JSON_MERGE_PATCH('[1, 2]', '["a", "b", "c"]', '[true, false]') AS Patch;
+------------------------------------+---------------+
| Preserve                           | Patch         |
+------------------------------------+---------------+
| [1, 2, "a", "b", "c", true, false] | [true, false] |
+------------------------------------+---------------+

合并对象

多个对象合并后产生一个对象。

• JSON_MERGE_PRESERVE() 在处理具有相同键的多个对象时，会将该键的所有唯一值组合并到一个数组中，被当作结果中该键的值。
• JSON_MERGE_PATCH() 会从左到右丢弃键值重复的值，因此结果只包含该键值的最后一个值。

mysql> SELECT
    -> JSON_MERGE_PRESERVE('{"a": 1, "b": 2}', '{"c": 3, "a": 4}', '{"c": 5, "d": 3}') AS Preserve,
    -> JSON_MERGE_PATCH('{"a": 3, "b": 2}', '{"c": 3, "a": 4}', '{"c": 5, "d": 3}') AS Patch;
+--------------------------------------------+----------------------------------+
| Preserve                                   | Patch                            |
+--------------------------------------------+----------------------------------+
| {"a": [1, 4], "b": 2, "c": [3, 5], "d": 3} | {"a": 4, "b": 2, "c": 5, "d": 3} |
+--------------------------------------------+----------------------------------+

合并元素

在需要数组值的上下文中使用的非数组值将被自动封装，然后将其转化为由 [ 和 ]字符包围的数组。

在下面的例子中，每个参数都被自动封装为一个数组（[1], [2]），与前两种情况一样，JSON_MERGE_PRESERVE() 会合并具有相同键值的值，而 JSON_MERGE_PATCH() 则会丢弃除最后一个键值外的所有重复键值。

mysql> SELECT
    -> JSON_MERGE_PRESERVE('1', '2') AS Preserve,
    -> JSON_MERGE_PATCH('1', '2') AS Patch;
+----------+-------+
| Preserve | Patch |
+----------+-------+
| [1, 2]   | 2     |
+----------+-------+

数组和对象合并

数组和对象值的合并方式是将对象自动封装为数组，并根据选择的合并函数（JSON_MERGE_PRESERVE() 或 JSON_MERGE_PATCH() ），分别通过合并值或 “最后一个重复键获胜”的方式合并数组，如本示例所示：

mysql> SELECT 
    -> JSON_MERGE_PRESERVE('[10, 20]', '{"a": "x", "b": "y"}') AS Preserve,
    -> JSON_MERGE_PATCH('[10, 20]', '{"a": "x", "b": "y"}') AS Patch; 
+--------------------------------+----------------------+
| Preserve                       | Patch                |
+--------------------------------+----------------------+
| [10, 20, {"a": "x", "b": "y"}] | {"a": "x", "b": "y"} |
+--------------------------------+----------------------+

搜索和修改 JSON 值

路径表达式

JSON 路径表达式对于提取或修改 JSON 文档部分内容的函数非常有用，它可以指定在文档中的哪个位置进行操作。例如，下面的查询从 JSON 文档中提取键为 name 的成员值：

mysql> SELECT JSON_EXTRACT('{"name": "小明", "age" : 18, "sex" : 1}', '$.name'); 
+-------------------------------------------------------------------+
| JSON_EXTRACT('{"name": "小明", "age" : 18, "sex" : 1}', '$.name') |
+-------------------------------------------------------------------+
| "小明"                                                            |
+-------------------------------------------------------------------+

路径语法使用前导 $ 字符来表示所考虑的 JSON 文档，后面还可选择使用选择器来继续表示文档中更具体的部分：

• 键名后面的句点表示对象中具有给定键的成员。如果不带引号的键名在路径表达式中不合法（例如包含空格），则键名必须用双引号指定。

  -- 键名不合法
  mysql> SELECT JSON_EXTRACT('{"na me": "小明", "age" : 18, "sex" : 1}', '$.na me');
  ERROR 3143 (42000): Invalid JSON path expression. The error is around character position 5.
  
  -- 需要使用双引号包裹
  mysql> SELECT JSON_EXTRACT('{"na me": "小明", "age" : 18, "sex" : 1}', '$."na me"');
  +-----------------------------------------------------------------------+
  | JSON_EXTRACT('{"na me": "小明", "age" : 18, "sex" : 1}', '$."na me"') |
  +-----------------------------------------------------------------------+
  | "小明"                                                                |
  +-----------------------------------------------------------------------+

• 将[N] 附加到选择数组的路径上（例如 $）表示指定数组中位置为 N 的值。数组位置是以 0 开头的整数。如果 path 没有选择数组值，则 path 的值与 path[0] 相同：

  mysql> SELECT JSON_SET('"x"', '$[0]', 'a');
  +------------------------------+
  | JSON_SET('"x"', '$[0]', 'a') |
  +------------------------------+
  | "a"                          |
  +------------------------------+
  
  mysql> SELECT JSON_SET('"x"', '$', 'a');    
  +---------------------------+
  | JSON_SET('"x"', '$', 'a') |
  +---------------------------+
  | "a"                       |
  +---------------------------+
  
  mysql> SELECT JSON_SET('"x"', '$[1]', 'a');  
  +------------------------------+
  | JSON_SET('"x"', '$[1]', 'a') |
  +------------------------------+
  | ["x", "a"]                   |
  +------------------------------+

• [M to N] 指定数组值的子集或范围，从位置 M 的值开始，到位置 N 的值结束。

  last 作为最右边数组元素索引同样被支持，

  mysql> SELECT JSON_SET('{"name": "John", "age": 30, "city": "New York"}', '$[last]', 'Los Angeles');
  +---------------------------------------------------------------------------------------+
  | JSON_SET('{"name": "John", "age": 30, "city": "New York"}', '$[last]', 'Los Angeles') |
  +---------------------------------------------------------------------------------------+
  | "Los Angeles"                                                                         |
  +---------------------------------------------------------------------------------------+

• 路径可以包含 * 或 ** 通配符：

  • .[*]表示为 JSON 对象中所有成员的值。
  • [*] 表示为 JSON 数组中所有元素的值。
  • prefix**suffix 会匹配以指定前缀开头、以指定后缀结尾的所有路径。

• 获取文档中不存在的路径（求值为不存在的数据）时会返回 NULL。

举个例子：我们使用 $ 来表示下面这个包含三个元素的 JSON 数组：

[3, {"a": [5, 6], "b": 10}, [99, 100]]

• $[0] 表示 3.
• $[1] 表示 {"a": [5, 6], "b": 10}.
• $[2] 表示 [99, 100].
• $[3] 返回的结果为 NULL （它指的是第四个数组元素，该元素不存在）。

由于 $[1] 和 $[2] 表示非标量值，因此它们可以用作选择嵌套值的更具体路径表达式的基础。例子：

• $[1].a 表示 [5, 6].
• $[1].b 表示 10.
• $[2][0] 表示 99.

使用通配符的路径计算结果为可以包含多个值的数组：

mysql> SELECT JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}', '$.*');
+---------------------------------------------------------+
| JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}', '$.*') |
+---------------------------------------------------------+
| [1, 2, [3, 4, 5]]                                       |
+---------------------------------------------------------+

mysql> SELECT JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}', '$.c[*]');
+------------------------------------------------------------+
| JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}', '$.c[*]') |
+------------------------------------------------------------+
| [3, 4, 5]                                                  |
+------------------------------------------------------------+

在以下示例中，路径$**.b 表示为多个路径（$.a.b和 $.c.b）并生成匹配路径值的数组：

mysql> SELECT JSON_EXTRACT('{"a": {"b": 1}, "c": {"b": 2}}', '$**.b');
+---------------------------------------------------------+
| JSON_EXTRACT('{"a": {"b": 1}, "c": {"b": 2}}', '$**.b') |
+---------------------------------------------------------+
| [1, 2]                                                  |
+---------------------------------------------------------+

JSON 数组的范围。 可以使用带有 to 关键字的范围来指定 JSON 数组的子集。例如，$[1 to 3]包含数组的第二个、第三个和第四个元素，如下所示：

mysql> SELECT JSON_EXTRACT('[1, 2, 3, 4, 5]', '$[1 to 3]');
+----------------------------------------------+
| JSON_EXTRACT('[1, 2, 3, 4, 5]', '$[1 to 3]') |
+----------------------------------------------+
| [2, 3, 4]                                    |
+----------------------------------------------+

语法 M to N，其中 M 和 N 分别是 JSON 数组元素所取范围的第一个和最后一个索引。数组元素的索引从 0 开始。

最右边的数组元素： last 关键字是数组中最后一个元素索引的代名词。last - N 形式的表达式可用于相对寻址和范围定义，如下所示：

mysql> SELECT JSON_EXTRACT('[1, 2, 3, 4, 5]', '$[last-3 to last-1]');
+--------------------------------------------------------+
| JSON_EXTRACT('[1, 2, 3, 4, 5]', '$[last-3 to last-1]') |
+--------------------------------------------------------+
| [2, 3, 4]                                              |
+--------------------------------------------------------+

如果路径表达式是针对一个非数组的值进行求值，求值结果与该值被包在单元素数组中的结果相同：

mysql> SELECT JSON_REPLACE('"Sakila"', '$[last]', 10);
+-----------------------------------------+
| JSON_REPLACE('"Sakila"', '$[last]', 10) |
+-----------------------------------------+
| 10                                      |
+-----------------------------------------+

JSON值搜索

我们可以使用带有 JSON 列标识符和 JSON 路径表达式的 column->path 作为 JSON_EXTRACT(column,path) 的同义词。

• JSON_CONTAINS (target, candidate[, path])

  通过返回 1 或 0 表示给定的 candidate 是否包含在目标 JSON 文档中，或者（如果提供了路径参数）候选文档是否在目标文档的特定路径中找到。如果任何参数为 NULL，或者路径参数未指定目标文档的某个部分，则返回 NULL。如果目标或候选对象不是有效的 JSON 文档，或者路径参数不是有效的路径表达式或包含 * 或 ** 通配符，则会发生错误。仅检查路径中是否存在任何数据，请改用 JSON_CONTAINS_PATH()。

  mysql> SET @j = '{"a": 1, "b": 2, "c": {"d": 4}}';
  mysql> SET @j2 = '1';
  mysql> SELECT JSON_CONTAINS(@j, @j2, '$.a');
  +-------------------------------+
  | JSON_CONTAINS(@j, @j2, '$.a') |
  +-------------------------------+
  |                             1 |
  +-------------------------------+
  mysql> SELECT JSON_CONTAINS(@j, @j2, '$.b');
  +-------------------------------+
  | JSON_CONTAINS(@j, @j2, '$.b') |
  +-------------------------------+
  |                             0 |
  +-------------------------------+
  
  mysql> SET @j2 = '{"d": 4}';
  mysql> SELECT JSON_CONTAINS(@j, @j2, '$.a');
  +-------------------------------+
  | JSON_CONTAINS(@j, @j2, '$.a') |
  +-------------------------------+
  |                             0 |
  +-------------------------------+
  mysql> SELECT JSON_CONTAINS(@j, @j2, '$.c');
  +-------------------------------+
  | JSON_CONTAINS(@j, @j2, '$.c') |
  +-------------------------------+
  |                             1 |
  +-------------------------------+

• JSON_EXTRACT(json_doc, path[, path] ...)

  从 JSON 文档返回数据，该数据是从与参数 path 匹配的文档部分中选择的。如果有任何参数 NULL或没有路径在文档中找到值，则返回 NULL。如果参数不是有效的 JSON 文档或任何 path 参数都不是有效的路径表达式， 则会发生错误 。

  mysql> SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]');
  +--------------------------------------------+
  | JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]') |
  +--------------------------------------------+
  | 20                                         |
  +--------------------------------------------+
  mysql> SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]', '$[0]');
  +----------------------------------------------------+
  | JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]', '$[0]') |
  +----------------------------------------------------+
  | [20, 10]                                           |
  +----------------------------------------------------+
  mysql> SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[2][*]');
  +-----------------------------------------------+
  | JSON_EXTRACT('[10, 20, [30, 40]]', '$[2][*]') |
  +-----------------------------------------------+
  | [30, 40]                                      |
  +-----------------------------------------------+

  MySQL 支持 -> 运算符作为此函数的简写形式，它可以与 2 个参数一起使用，其中左侧是 JSON 列标识符（不是表达式），右侧是要在列中匹配的 JSON 路径。

• column ->path （此功能不限于 SELECT）

  mysql> SELECT c, JSON_EXTRACT(c, "$.id"), g
       > FROM jemp
       > WHERE JSON_EXTRACT(c, "$.id") > 1
       > ORDER BY JSON_EXTRACT(c, "$.name");
  +-------------------------------+-----------+------+
  | c                             | c->"$.id" | g    |
  +-------------------------------+-----------+------+
  | {"id": "3", "name": "Barney"} | "3"       |    3 |
  | {"id": "4", "name": "Betty"}  | "4"       |    4 |
  | {"id": "2", "name": "Wilma"}  | "2"       |    2 |
  +-------------------------------+-----------+------+
  3 rows in set (0.00 sec)
  
  mysql> SELECT c, c->"$.id", g
       > FROM jemp
       > WHERE c->"$.id" > 1
       > ORDER BY c->"$.name";
  +-------------------------------+-----------+------+
  | c                             | c->"$.id" | g    |
  +-------------------------------+-----------+------+
  | {"id": "3", "name": "Barney"} | "3"       |    3 |
  | {"id": "4", "name": "Betty"}  | "4"       |    4 |
  | {"id": "2", "name": "Wilma"}  | "2"       |    2 |
  +-------------------------------+-----------+------+

• column ->> path

  这是一个改进的、不带引号的提取运算符。虽然该->运算符只是提取一个值，但该->>运算符还取消提取引用的结果。该运算符可以在任何允许的->>地方使用 。

  mysql> SELECT * FROM jemp WHERE g > 2;
  +-------------------------------+------+
  | c                             | g    |
  +-------------------------------+------+
  | {"id": "3", "name": "Barney"} |    3 |
  | {"id": "4", "name": "Betty"}  |    4 |
  +-------------------------------+------+
  2 rows in set (0.01 sec)
  
  mysql> SELECT c->'$.name' AS name
      ->     FROM jemp WHERE g > 2;
  +----------+
  | name     |
  +----------+
  | "Barney" |
  | "Betty"  |
  +----------+
  2 rows in set (0.00 sec)
  
  mysql> SELECT JSON_UNQUOTE(c->'$.name') AS name
      ->     FROM jemp WHERE g > 2;
  +--------+
  | name   |
  +--------+
  | Barney |
  | Betty  |
  +--------+
  2 rows in set (0.00 sec)
  
  mysql> SELECT c->>'$.name' AS name
      ->     FROM jemp WHERE g > 2;
  +--------+
  | name   |
  +--------+
  | Barney |
  | Betty  |
  +--------+
  2 rows in set (0.00 sec)

• JSON_VALUE(json_doc, path)

  从指定文档中给定路径处的 JSON 文档中提取值，并返回提取的值，可以选择将其转换为所需的类型。完整的语法如下所示：

  JSON_VALUE(json_doc, path [RETURNING type] [on_empty] [on_error])
  
  on_empty:
      {NULL | ERROR | DEFAULT value} ON EMPTY
  
  on_error:
      {NULL | ERROR | DEFAULT value} ON ERROR

  例子：

  mysql> SELECT JSON_VALUE('{"fname": "Joe", "lname": "Palmer"}', '$.fname');
  +--------------------------------------------------------------+
  | JSON_VALUE('{"fname": "Joe", "lname": "Palmer"}', '$.fname') |
  +--------------------------------------------------------------+
  | Joe                                                          |
  +--------------------------------------------------------------+
  
  mysql> SELECT JSON_VALUE('{"item": "shoes", "price": "49.95"}', '$.price'
      -> RETURNING DECIMAL(4,2)) AS price;
  +-------+
  | price |
  +-------+
  | 49.95 |
  +-------+

参考

1. https://dev.mysql.com/doc/refman/8.0/en/json.html ↩