MySQL8.0 JSON函数之搜索JSON值（五）

DBA的辛酸事儿 2021-06-10

1696

之前的几篇文章介绍了JSON数据类型，相信大家已经对JSON有了一定的了解，上面一篇文章介绍了《MySQL8.0 JSON函数之创建与返回JSON属性（四）》JSON函数的使用；本节中的函数对JSON值执行搜索或比较操作，以从中提取数据；

JSON_CONTAINS(target, candidate[, path])

通过返回1或0指示给定的candidate是否包含在目标JSON文档中，或者（如果提供了path 参数）指示是否在目标内的特定路径上找到了候选对象。如果任何参数为NULL，或者路径参数未标识目标文档的节，则返回NULL。如果target或 candidate不是有效的JSON文档，或者path参数不是有效的路径表达式或包含 *或**通配符，则会发生错误。

从MySQL 8.0.17开始，可以使用多值索引JSON_CONTAINS()对在 InnoDB表上使用的查询进行优化。关于MySQL 8.0多值索引详细可参考：MySQL 8.0 新特性:多值索引 --如何给JSON数组添加索引（三）

mysql>SET @j = '{"a": 1, "b": 2, "c": {"d": 4}}';
Query OK, 0 rows affected (0.00 sec)
 
mysql>SET @j2 = '1';
Query OK, 0 rows affected (0.00 sec)
 
mysql>SELECT JSON_CONTAINS(@j, @j2, '$.a');
+-------------------------------+
| JSON_CONTAINS(@j, @j2, '$.a') |
+-------------------------------+
|                             1 |
+-------------------------------+
1 row in set (0.00 sec)
 
mysql>SELECT JSON_CONTAINS(@j, @j2, '$.b');
+-------------------------------+
| JSON_CONTAINS(@j, @j2, '$.b') |
+-------------------------------+
|                             0 |
+-------------------------------+
1 row in set (0.00 sec)
 
mysql> SET @j2 = '{"d": 4}';
Query OK, 0 rows affected (0.00 sec)
 
mysql> SELECT JSON_CONTAINS(@j, @j2, '$.a');
+-------------------------------+
| JSON_CONTAINS(@j, @j2, '$.a') |
+-------------------------------+
|                             0 |
+-------------------------------+
1 row in set (0.00 sec)
 
mysql>SELECT JSON_CONTAINS(@j, @j2, '$.c');
+-------------------------------+
| JSON_CONTAINS(@j, @j2, '$.c') |
+-------------------------------+
|                             1 |
+-------------------------------+
1 row in set (0.00 sec)
复制

JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] …)

返回0或1以指示JSON文档是否包含给定路径中的数据。返回NULL 是否有任何参数NULL。如果json_doc参数不是有效的JSON文档，任何path 参数不是有效的路径表达式，或者 one_or_all不是 ‘one’或，都会发生错误’all’。

如果文档中没有指定的路径，则返回值为0。否则，返回值取决于 one_or_all参数：

‘one’：如果文档中至少存在一个路径，则为1，否则为0。
‘all’：如果文档中所有路径都存在，则为1，否则为0。

mysql>SET @j = '{"a": 1, "b": 2, "c": {"d": 4}}';
Query OK, 0 rows affected (0.00 sec)
 
mysql>SELECT JSON_CONTAINS_PATH(@j, 'one', '$.a', '$.e');
+---------------------------------------------+
| JSON_CONTAINS_PATH(@j, 'one', '$.a', '$.e') |
+---------------------------------------------+
|                                           1 |
+---------------------------------------------+
1 row in set (0.00 sec)
 
mysql>SELECT JSON_CONTAINS_PATH(@j, 'all', '$.a', '$.e');
+---------------------------------------------+
| JSON_CONTAINS_PATH(@j, 'all', '$.a', '$.e') |
+---------------------------------------------+
|                                           0 |
+---------------------------------------------+
1 row in set (0.00 sec)
 
mysql>SELECT JSON_CONTAINS_PATH(@j, 'one', '$.c.d');
+----------------------------------------+
| JSON_CONTAINS_PATH(@j, 'one', '$.c.d') |
+----------------------------------------+
|                                      1 |
+----------------------------------------+
1 row in set (0.00 sec)
 
mysql>SELECT JSON_CONTAINS_PATH(@j, 'one', '$.a.d');
+----------------------------------------+
| JSON_CONTAINS_PATH(@j, 'one', '$.a.d') |
+----------------------------------------+
|                                      0 |
+----------------------------------------+
1 row in set (0.00 sec)
复制

JSON_EXTRACT(json_doc, path[, path] …)

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

返回值由path参数匹配的所有值组成。如果这些参数有可能返回多个值，则匹配的值将按照与生成它们的路径相对应的顺序自动包装为一个数组。否则，返回值是单个匹配值。

mysql>SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]');
+--------------------------------------------+
| JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]') |
+--------------------------------------------+
| 20                                         |
+--------------------------------------------+
1 row in set (0.00 sec)
 
mysql> SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]', '$[0]');
+----------------------------------------------------+
| JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]', '$[0]') |
+----------------------------------------------------+
| [20, 10]                                           |
+----------------------------------------------------+
1 row in set (0.00 sec)
 
mysql>SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[2][*]');
+-----------------------------------------------+
| JSON_EXTRACT('[10, 20, [30, 40]]', '$[2][*]') |
+-----------------------------------------------+
| [30, 40]                                      |
+-----------------------------------------------+
1 row in set (0.00 sec)
 
mysql>SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[2][2]');      
+-----------------------------------------------+
| JSON_EXTRACT('[10, 20, [30, 40]]', '$[2][2]') |
+-----------------------------------------------+
| NULL                                          |
+-----------------------------------------------+
1 row in set (0.00 sec)
复制

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

column->path

当与两个参数一起使用时，该 -> 运算符用作该JSON_EXTRACT()函数的别名，左侧是列标识符，右侧是根据JSON文档（列值）评估的JSON路径。您可以在SQL语句中的任何位置使用此类表达式代替列标识符。

SELECT此处显示的两个语句产生相同的输出：

mysql>SELECT c, JSON_EXTRACT(c, "$.id"), g
    -> FROM jemp
    -> WHERE JSON_EXTRACT(c, "$.id") > 1
    -> ORDER BY JSON_EXTRACT(c, "$.name");
+-------------------------------+-------------------------+------+
| c                             | JSON_EXTRACT(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.10 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 |
+-------------------------------+-----------+------+
3 rows in set (0.00 sec)
复制

此功能不限于 SELECT，如下所示：

mysql>ALTER TABLE jemp ADD COLUMN n INT;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql>UPDATE jemp SET n=1 WHERE c->"$.id" = "4";
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
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 |
+-------------------------------+-----------+------+
3 rows in set (0.00 sec)
 
mysql>DELETE FROM jemp WHERE c->"$.id" = "4";
Query OK, 1 row affected (0.01 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": "2", "name": "Wilma"}  | "2"       |    2 |
+-------------------------------+-----------+------+
2 rows in set (0.00 sec)
复制

此方式也适用于JSON数组值，如下所示：

mysql>CREATE TABLE tj10 (a JSON, b INT);
Query OK, 0 rows affected (0.04 sec)
 
mysql>INSERT INTO tj10 VALUES ("[3,10,5,17,44]", 33), ("[3,10,5,17,[22,44,66]]", 0);
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0
 
mysql>select * from tj10;
+------------------------------+------+
| a                            | b    |
+------------------------------+------+
| [3, 10, 5, 17, 44]           |   33 |
| [3, 10, 5, 17, [22, 44, 66]] |    0 |
+------------------------------+------+
2 rows in set (0.00 sec)
 
mysql>SELECT a->"$[4]" FROM tj10;
+--------------+
| a->"$[4]"    |
+--------------+
| 44           |
| [22, 44, 66] |
+--------------+
2 rows in set (0.00 sec)
 
mysql>SELECT * FROM tj10 WHERE a->"$[0]" = 3;
+------------------------------+------+
| a                            | b    |
+------------------------------+------+
| [3, 10, 5, 17, 44]           |   33 |
| [3, 10, 5, 17, [22, 44, 66]] |    0 |
+------------------------------+------+
2 rows in set (0.00 sec)
 
mysql>SELECT * FROM tj10 WHERE a->"$[4]" = 3;
Empty set (0.00 sec)
 
mysql>SELECT * FROM tj10 WHERE a->"$[4]" = 44;
+--------------------+------+
| a                  | b    |
+--------------------+------+
| [3, 10, 5, 17, 44] |   33 |
+--------------------+------+
1 row in set (0.00 sec)
复制

支持嵌套数组。如果在目标JSON文档中找不到匹配的键，则使用->的表达式将计算为NULL，如下所示：

mysql>SELECT * FROM tj10 WHERE a->"$[4][1]" IS NOT NULL;
+------------------------------+------+
| a                            | b    |
+------------------------------+------+
| [3, 10, 5, 17, [22, 44, 66]] |    0 |
+------------------------------+------+
1 row in set (0.00 sec)
 
mysql>SELECT a->"$[4][1]" FROM tj10;
+--------------+
| a->"$[4][1]" |
+--------------+
| NULL         |
| 44           |
+--------------+
2 rows in set (0.00 sec)
复制

这与在使用JSON_EXTRACT()以下情况时看到的行为相同：

mysql >SELECT JSON_EXTRACT(a, "$[4][1]") FROM tj10;
+----------------------------+
| JSON_EXTRACT(a, "$[4][1]") |
+----------------------------+
| NULL                       |
| 44                         |
+----------------------------+
2 rows in set (0.00 sec)
复制

column->>path

这是一种改进的单引号提取运算符。而->操作者简单地提取的值时，->>在加法运算unquotes提取结果。换句话说，给定一个 JSON列值 column和一个路径表达式 path，以下三个表达式将返回相同的值：

* JSON_UNQUOTE( JSON_EXTRACT(column, path) )
* JSON_UNQUOTE(column -> path)
* column->>path

可以在任何允许使用JSON_UNQUOTE(JSON_EXTRACT())的地方使用->> 。这包括（但不限于）SELECT lists、WHERE和HAVING子句以及ORDER BY和GROUP BY子句。

接下来的几条语句演示了->>与mysql客户端中其他表达式的一些运算符等效项：

mysql> SELECT * FROM jemp WHERE g > 2;
+-------------------------------+------+------+
| c                             | g    | n    |
+-------------------------------+------+------+
| {"id": "3", "name": "Barney"} |    3 | NULL |
+-------------------------------+------+------+
1 row in set (0.01 sec)
 
mysql>SELECT c->'$.name' AS name FROM jemp WHERE g > 2;
+----------+
| name     |
+----------+
| "Barney" |
+----------+
1 row in set (0.00 sec)
 
mysql>SELECT JSON_UNQUOTE(c->'$.name') AS name FROM jemp WHERE g > 2;
+--------+
| name   |
+--------+
| Barney |
+--------+
1 row in set (0.00 sec)
 
mysql>SELECT c->>'$.name' AS name FROM jemp WHERE g > 2;
+--------+
| name   |
+--------+
| Barney |
+--------+
1 row in set (0.00 sec)
复制

此运算符也可以与JSON数组一起使用，如下所示：

mysql>INSERT INTO tj10 VALUES  ('[3,10,5,"x",44]', 33),('[3,10,5,17,[22,"y",66]]', 0);
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0
 
mysql>SELECT a->"$[3]", a->"$[4][1]" FROM tj10;                                       
+-----------+--------------+
| a->"$[3]" | a->"$[4][1]" |
+-----------+--------------+
| 17        | NULL         |
| 17        | 44           |
| "x"       | NULL         |
| 17        | "y"          |
+-----------+--------------+
4 rows in set (0.00 sec)
 
mysql>SELECT a->>"$[3]", a->>"$[4][1]" FROM tj10;                                     
+------------+---------------+
| a->>"$[3]" | a->>"$[4][1]" |
+------------+---------------+
| 17         | NULL          |
| 17         | 44            |
| x          | NULL          |
| 17         | y             |
+------------+---------------+
4 rows in set (0.00 sec)
复制

* 与一样 ->，->>运算符总是在的输出中扩展EXPLAIN，如以下示例所示：

mysql>explain SELECT c->>'$.name' AS name FROM jemp WHERE g > 2;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | jemp  | NULL       | range | i             | i    | 5       | NULL |    1 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
 
mysql>show warnings;
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                    |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | * select#1 */ select json_unquote(json_extract(`wjqdb`.`jemp`.`c`,'$.name')) AS `name` from `wjqdb`.`jemp` where (`wjqdb`.`jemp`.`g` > 2) |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
复制

JSON_KEYS(json_doc[, path])

以JSON数组的形式返回JSON对象的顶级值中的键，如果给定了path参数，则返回所选路径中的顶级键。如果任何参数为NULL，json_doc参数不是对象，或者path（如果给定）未定位对象，则返回NULL。如果json_doc参数不是有效的json文档，或者path参数不是有效的路径表达式，或者包含*或**通配符，则会发生错误。

如果选定对象为空，则结果数组为空。如果顶级值具有嵌套的子对象，则返回值不包括来自这些子对象的键。

mysql>SELECT JSON_KEYS('{"a": 1, "b": {"c": 30}}');
+---------------------------------------+
| JSON_KEYS('{"a": 1, "b": {"c": 30}}') |
+---------------------------------------+
| ["a", "b"]                            |
+---------------------------------------+
1 row in set (0.00 sec)
 
mysql>SELECT JSON_KEYS('{"a": 1, "b": {"c": 30}}', '$.b');
+----------------------------------------------+
| JSON_KEYS('{"a": 1, "b": {"c": 30}}', '$.b') |
+----------------------------------------------+
| ["c"]                                        |
+----------------------------------------------+
1 row in set (0.00 sec)
 
mysql>SELECT JSON_KEYS('{"a": 1, "b": {"c": 30}}', '$.a');
+----------------------------------------------+
| JSON_KEYS('{"a": 1, "b": {"c": 30}}', '$.a') |
+----------------------------------------------+
| NULL                                         |
+----------------------------------------------+
1 row in set (0.00 sec)
复制

JSON_OVERLAPS(json_doc1, json_doc2)

JSON_OVERLAPS() 已在MySQL 8.0.17中添加。此函数相当于JSON_CONTAINS（），它要求所搜索的数组中的所有元素都存在于所搜索的数组中。因此，JSON_CONTAINS（）对搜索键执行AND操作，而JSON_OVERLAPS（）执行OR操作。

在WHERE子句中使用JSON_OVERLAPS（）对InnoDB表的JSON列的查询可以使用多值索引进行优化。多值索引，提供了详细的信息和示例。JSON_OVERLAPS()WHERE比较两个数组时，JSON_OVERLAPS() 如果它们共享一个或多个数组元素，则返回true；否则，返回false：

mysql>SELECT JSON_OVERLAPS("[1,3,5,7]", "[2,6,7]");
+---------------------------------------+
| JSON_OVERLAPS("[1,3,5,7]", "[2,6,7]") |
+---------------------------------------+
|                                     1 |
+---------------------------------------+
1 row in set (0.00 sec)
 
mysql>SELECT JSON_OVERLAPS("[1,3,5,7]", "[2,6,8]");
+---------------------------------------+
| JSON_OVERLAPS("[1,3,5,7]", "[2,6,8]") |
+---------------------------------------+
|                                     0 |
+---------------------------------------+
1 row in set (0.00 sec)
 
mysql>SELECT JSON_OVERLAPS('{"a": 1, "b": {"c": 30}}', '{"a": 1, "b": 20}');   
+----------------------------------------------------------------+
| JSON_OVERLAPS('{"a": 1, "b": {"c": 30}}', '{"a": 1, "b": 20}') |
+----------------------------------------------------------------+
|                                                              1 |
+----------------------------------------------------------------+
1 row in set (0.00 sec)
 
mysql>SELECT JSON_OVERLAPS('{"a": 1, "b": {"c": 30}}', '{"a": 1, "d": 20}');
+----------------------------------------------------------------+
| JSON_OVERLAPS('{"a": 1, "b": {"c": 30}}', '{"a": 1, "d": 20}') |
+----------------------------------------------------------------+
|                                                              1 |
+----------------------------------------------------------------+
1 row in set (0.00 sec)
 
mysql >SELECT JSON_OVERLAPS('{"a": 1, "b": {"c": 30}}', '{"c": 1, "d": 20}');
+----------------------------------------------------------------+
| JSON_OVERLAPS('{"a": 1, "b": {"c": 30}}', '{"c": 1, "d": 20}') |
+----------------------------------------------------------------+
|                                                              0 |
+----------------------------------------------------------------+
1 row in set (0.00 sec)
复制

部分匹配被视为不匹配，如下所示：

mysql>SELECT JSON_OVERLAPS('[[1,2],[3,4],5]', '[1,[2,3],[4,5]]');
+-----------------------------------------------------+
| JSON_OVERLAPS('[[1,2],[3,4],5]', '[1,[2,3],[4,5]]') |
+-----------------------------------------------------+
|                                                   0 |
+-----------------------------------------------------+
1 row in set (0.00 sec)
复制

比较对象时，如果它们至少有一个共同的键-值对，则结果为true。

mysql>SELECT JSON_OVERLAPS('{"a":1,"b":10,"d":10}', '{"c":1,"e":10,"f":1,"d":10}');
+-----------------------------------------------------------------------+
| JSON_OVERLAPS('{"a":1,"b":10,"d":10}', '{"c":1,"e":10,"f":1,"d":10}') |
+-----------------------------------------------------------------------+
|                                                                     1 |
+-----------------------------------------------------------------------+
1 row in set (0.00 sec)
 
mysql>SELECT JSON_OVERLAPS('{"a":1,"b":10,"d":10}', '{"a":5,"e":10,"f":1,"d":20}');
+-----------------------------------------------------------------------+
| JSON_OVERLAPS('{"a":1,"b":10,"d":10}', '{"a":5,"e":10,"f":1,"d":20}') |
+-----------------------------------------------------------------------+
|                                                                     0 |
+-----------------------------------------------------------------------+
1 row in set (0.00 sec)
复制

如果将两个标量用作函数的参数，请 JSON_OVERLAPS()执行一个简单的相等性测试：

mysql>SELECT JSON_OVERLAPS('5', '5');
+-------------------------+
| JSON_OVERLAPS('5', '5') |
+-------------------------+
|                       1 |
+-------------------------+
1 row in set (0.00 sec)
 
mysql dba_admin@127.0.0.1:wjqdb18:20:28>SELECT JSON_OVERLAPS('5', '6');
+-------------------------+
| JSON_OVERLAPS('5', '6') |
+-------------------------+
|                       0 |
+-------------------------+
1 row in set (0.00 sec)
复制

将标量与数组进行比较时，请 JSON_OVERLAPS()尝试将标量视为数组元素。在此示例中，第二个参数6解释为 [6]，如下所示：

mysql> SELECT JSON_OVERLAPS('[4,5,6,7]', '6');
+---------------------------------+
| JSON_OVERLAPS('[4,5,6,7]', '6') |
+---------------------------------+
|                               1 |
+---------------------------------+
1 row in set (0.00 sec)
复制

该函数不执行类型转换：

mysql>SELECT JSON_OVERLAPS('[4,5,"6",7]', '6');
+-----------------------------------+
| JSON_OVERLAPS('[4,5,"6",7]', '6') |
+-----------------------------------+
|                                 0 |
+-----------------------------------+
1 row in set (0.00 sec)
 
mysql dba_admin@127.0.0.1:wjqdb18:24:02>SELECT JSON_OVERLAPS('[4,5,6,7]', '"6"');
+-----------------------------------+
| JSON_OVERLAPS('[4,5,6,7]', '"6"') |
+-----------------------------------+
|                                 0 |
+-----------------------------------+
1 row in set (0.00 sec)
复制

JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] …])

返回JSON文档中给定字符串的路径。如果任何一个json_doc，path或 search_str 参数为NULL，则返回NULL；文档中不存在路径；或找不到搜索字符串。如果json_doc参数不是有效的JSON文档，任何 path参数不是有效的路径表达式，one或all不是’one’或’all’，或者 escape_char不是常量表达式，都会发生错误。该one_or_all参数会影响搜索，如下所示：

‘one’：搜索在第一个匹配项后终止，并返回一个路径字符串。未定义首先考虑哪个匹配。
‘all’：搜索将返回所有匹配的路径字符串，因此不包括重复的路径。如果有多个字符串，它们将自动包装为一个数组。数组元素的顺序是不确定的。

在search_str搜索字符串参数中，%和_ 字符的作用与LIKE 运算符相同：%匹配任意数量的字符（包括零个字符），并 _恰好匹配一个字符。要在搜索字符串中指定文字%或 _字符，请在其前面加上转义字符。默认值是 \，如果 escape_char参数丢失或 NULL。否则， escape_char必须为空或一个字符的常量。

search_str and path总是被解释为utf8mb4字符串，而不管它们的实际编码是什么。这是MySQL 8.0.24中修复的已知问题（Bug#32449181）。

mysql>SET @j = '["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]';
Query OK, 0 rows affected (0.00 sec)
 
mysql>SELECT JSON_SEARCH(@j, 'one', 'abc');
+-------------------------------+
| JSON_SEARCH(@j, 'one', 'abc') |
+-------------------------------+
| "$[0]"                        |
+-------------------------------+
1 row in set (0.00 sec)
 
mysql> SELECT JSON_SEARCH(@j, 'all', 'abc');
+-------------------------------+
| JSON_SEARCH(@j, 'all', 'abc') |
+-------------------------------+
| ["$[0]", "$[2].x"]            |
+-------------------------------+
1 row in set (0.01 sec)
 
mysql>SELECT JSON_SEARCH(@j, 'all', 'ghi');
+-------------------------------+
| JSON_SEARCH(@j, 'all', 'ghi') |
+-------------------------------+
| NULL                          |
+-------------------------------+
1 row in set (0.00 sec)
 
mysql>SELECT JSON_SEARCH(@j, 'all', '10');
+------------------------------+
| JSON_SEARCH(@j, 'all', '10') |
+------------------------------+
| "$[1][0].k"                  |
+------------------------------+
1 row in set (0.00 sec)
 
mysql>SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$');
+-----------------------------------------+
| JSON_SEARCH(@j, 'all', '10', NULL, '$') |
+-----------------------------------------+
| "$[1][0].k"                             |
+-----------------------------------------+
1 row in set (0.00 sec)
 
mysql>SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$[*]');
+--------------------------------------------+
| JSON_SEARCH(@j, 'all', '10', NULL, '$[*]') |
+--------------------------------------------+
| "$[1][0].k"                                |
+--------------------------------------------+
1 row in set (0.00 sec)
 
mysql>SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$**.k');
+---------------------------------------------+
| JSON_SEARCH(@j, 'all', '10', NULL, '$**.k') |
+---------------------------------------------+
| "$[1][0].k"                                 |
+---------------------------------------------+
1 row in set (0.00 sec)
 
mysql>SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$[*][0].k');
+-------------------------------------------------+
| JSON_SEARCH(@j, 'all', '10', NULL, '$[*][0].k') |
+-------------------------------------------------+
| "$[1][0].k"                                     |
+-------------------------------------------------+
1 row in set (0.00 sec)
 
mysql>SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$[1]');
+--------------------------------------------+
| JSON_SEARCH(@j, 'all', '10', NULL, '$[1]') |
+--------------------------------------------+
| "$[1][0].k"                                |
+--------------------------------------------+
1 row in set (0.00 sec)
 
mysql>SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$[1][0]');
+-----------------------------------------------+
| JSON_SEARCH(@j, 'all', '10', NULL, '$[1][0]') |
+-----------------------------------------------+
| "$[1][0].k"                                   |
+-----------------------------------------------+
1 row in set (0.00 sec)
 
mysql>SELECT JSON_SEARCH(@j, 'all', 'abc', NULL, '$[2]');
+---------------------------------------------+
| JSON_SEARCH(@j, 'all', 'abc', NULL, '$[2]') |
+---------------------------------------------+
| "$[2].x"                                    |
+---------------------------------------------+
1 row in set (0.00 sec)
 
mysql>SELECT JSON_SEARCH(@j, 'all', '%a%');
+-------------------------------+
| JSON_SEARCH(@j, 'all', '%a%') |
+-------------------------------+
| ["$[0]", "$[2].x"]            |
+-------------------------------+
1 row in set (0.00 sec)
 
mysql>SELECT JSON_SEARCH(@j, 'all', '%b%');
+-------------------------------+
| JSON_SEARCH(@j, 'all', '%b%') |
+-------------------------------+
| ["$[0]", "$[2].x", "$[3].y"]  |
+-------------------------------+
1 row in set (0.00 sec)
 
mysql>SELECT JSON_SEARCH(@j, 'all', '%b%', NULL, '$[0]');
+---------------------------------------------+
| JSON_SEARCH(@j, 'all', '%b%', NULL, '$[0]') |
+---------------------------------------------+
| "$[0]"                                      |
+---------------------------------------------+
1 row in set (0.00 sec)
 
mysql>SELECT JSON_SEARCH(@j, 'all', '%b%', NULL, '$[2]');
+---------------------------------------------+
| JSON_SEARCH(@j, 'all', '%b%', NULL, '$[2]') |
+---------------------------------------------+
| "$[2].x"                                    |
+---------------------------------------------+
1 row in set (0.00 sec)
 
mysql>SELECT JSON_SEARCH(@j, 'all', '%b%', NULL, '$[1]');
+---------------------------------------------+
| JSON_SEARCH(@j, 'all', '%b%', NULL, '$[1]') |
+---------------------------------------------+
| NULL                                        |
+---------------------------------------------+
1 row in set (0.00 sec)
 
mysql>SELECT JSON_SEARCH(@j, 'all', '%b%', '', '$[1]');
+-------------------------------------------+
| JSON_SEARCH(@j, 'all', '%b%', '', '$[1]') |
+-------------------------------------------+
| NULL                                      |
+-------------------------------------------+
1 row in set (0.00 sec)
 
mysql>SELECT JSON_SEARCH(@j, 'all', '%b%', '', '$[3]');
+-------------------------------------------+
| JSON_SEARCH(@j, 'all', '%b%', '', '$[3]') |
+-------------------------------------------+
| "$[3].y"                                  |
+-------------------------------------------+
1 row in set (0.00 sec)
复制

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

JSON_VALUE() 是MySQL 8.0.21中引入的。

例子。这里显示了两个简单的示例：

mysql>SELECT JSON_VALUE('{"fname": "Joe", "lname": "Palmer"}', '$.fname');
+--------------------------------------------------------------+
| JSON_VALUE('{"fname": "Joe", "lname": "Palmer"}', '$.fname') |
+--------------------------------------------------------------+
| Joe                                                          |
+--------------------------------------------------------------+
1 row in set (0.00 sec)
 
mysql>SELECT JSON_VALUE('{"item": "shoes", "price": "49.95"}', '$.price' RETURNING DECIMAL(4,2)) AS price;
+-------+
| price |
+-------+
| 49.95 |
+-------+
1 row in set (0.00 sec)
复制

value MEMBER OF(json_array)

如果value是的元素json_array，则返回true（1），否则返回false（0）。value必须是标量或JSON文档；如果它是标量，则运算符尝试将其视为JSON数组的元素。可以使用多值索引优化WHERE子句中InnoDB表的JSON列上使用MEMBER OF（）的查询。。

简单标量被视为数组值，如下所示：

mysql>SELECT 17 MEMBER OF('[23, "abc", 17, "ab", 10]');
+-------------------------------------------+
| 17 MEMBER OF('[23, "abc", 17, "ab", 10]') |
+-------------------------------------------+
|                                         1 |
+-------------------------------------------+
1 row in set (0.00 sec)
 
mysql>SELECT 'ab' MEMBER OF('[23, "abc", 17, "ab", 10]');
+---------------------------------------------+
| 'ab' MEMBER OF('[23, "abc", 17, "ab", 10]') |
+---------------------------------------------+
|                                           1 |
+---------------------------------------------+
1 row in set (0.00 sec)
 
mysql>SELECT 'a' MEMBER OF('[23, "abc", 17, "ab", 10]');
+--------------------------------------------+
| 'a' MEMBER OF('[23, "abc", 17, "ab", 10]') |
+--------------------------------------------+
|                                          0 |
+--------------------------------------------+
1 row in set (0.00 sec)
复制

数组元素值的部分匹配不匹配：

mysql>SELECT 7 MEMBER OF('[23, "abc", 17, "ab", 10]');
+------------------------------------------+
| 7 MEMBER OF('[23, "abc", 17, "ab", 10]') |
+------------------------------------------+
|                                        0 |
+------------------------------------------+
1 row in set (0.00 sec)
 
mysql>SELECT 'a' MEMBER OF('[23, "abc", 17, "ab", 10]');
+--------------------------------------------+
| 'a' MEMBER OF('[23, "abc", 17, "ab", 10]') |
+--------------------------------------------+
|                                          0 |
+--------------------------------------------+
1 row in set (0.00 sec)
复制

不执行与字符串类型之间的转换：

mysql>SELECT 17 MEMBER OF('[23, "abc", "17", "ab", 10]'),"17" MEMBER OF('[23, "abc", 17, "ab", 10]');
+---------------------------------------------+---------------------------------------------+
| 17 MEMBER OF('[23, "abc", "17", "ab", 10]') | "17" MEMBER OF('[23, "abc", 17, "ab", 10]') |
+---------------------------------------------+---------------------------------------------+
|                                           0 |                                           0 |
+---------------------------------------------+---------------------------------------------+
1 row in set (0.00 sec)
复制

与本身为数组的值一起使用，必须将其显式转换为JSON数组。您可以使用以下方法执行此操作CAST(… AS JSON)：

mysql>SELECT CAST('[4,5]' AS JSON) MEMBER OF('[[3,4],[4,5]]');
+--------------------------------------------------+
| CAST('[4,5]' AS JSON) MEMBER OF('[[3,4],[4,5]]') |
+--------------------------------------------------+
|                                                1 |
+--------------------------------------------------+
1 row in set (0.00 sec)
复制

也可以使用JSON_ARRAY()函数执行必要的强制转换，如下所示：

mysql >SELECT JSON_ARRAY(4,5) MEMBER OF('[[3,4],[4,5]]');
+--------------------------------------------+
| JSON_ARRAY(4,5) MEMBER OF('[[3,4],[4,5]]') |
+--------------------------------------------+
|                                          1 |
+--------------------------------------------+
1 row in set (0.00 sec)
复制

必须使用CAST(… AS JSON)或将任何用作测试值或出现在目标数组中的JSON对象强制为正确的类型 JSON_OBJECT()。此外，包含JSON对象的目标数组本身必须使用强制转换 JSON_ARRAY。下面的语句序列对此进行了演示：

mysql> SET @a = CAST('{"a":1}' AS JSON);
Query OK, 0 rows affected (0.00 sec)
 
mysql>SET @b = JSON_OBJECT("b", 2);
Query OK, 0 rows affected (0.00 sec)
 
mysql>SET @c = JSON_ARRAY(17, @b, "abc", @a, 23);
Query OK, 0 rows affected (0.00 sec)
 
mysql>SELECT @a MEMBER OF(@c), @b MEMBER OF(@c);
+------------------+------------------+
| @a MEMBER OF(@c) | @b MEMBER OF(@c) |
+------------------+------------------+
|                1 |                1 |
+------------------+------------------+
1 row in set (0.00 sec)
复制