专栏首页SEian.G学习记录MySQL8.0 JSON函数之搜索JSON值(五)

MySQL8.0 JSON函数之搜索JSON值(五)

之前的几篇文章介绍了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)

该MEMBER OF()操作符是在MySQL 8.0.17中添加的。

好了,今天就先介绍到这里,关于JSON函数更多内容,后续会慢慢进行介绍;

关联阅读

MySQL 8.0 JSON增强到底有多强?(一)

MySQL如何给JSON列添加索引(二)

MySQL 8.0 新特性:多值索引 --如何给JSON数组添加索引(三)

MySQL8.0 JSON函数之创建与返回JSON属性(四)

本文分享自微信公众号 - DBA的辛酸事儿(dbabitter),作者:SEian.G

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2021-06-10

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • MySQL8.0 JSON函数之创建与返回JSON属性(四)

    经过前面三篇的文章的介绍,相信大家已经对MySQL JSON数据类型有了一定的了解,为了在业务中更好的使用JSON类型,今天我们来具体介绍一下JSON函数的使用...

    SEian.G
  • MySQL 8.0 竟然可以直接操作json文档了。。。

    经过漫长的测试,即将整体迁移至Mysql8.0; Mysql8.0 对于Json操作新增/优化了很多相关Json的API操作; 阅读了一下官方文档,虽然绝大多数...

    用户1516716
  • MySQL 8.0.11 (2018-04-19, General Availability)

    仅支持通过使用 in-place 方式从 MySQL 5.7 升级到 MySQL 8.0 升级; 不支持从 MySQL 8.0 降级到 MySQL 5....

    MySQL轻松学
  • 关于MySQL 8.0的几个重点,都在这里

    在MySQL8.0中重新设计了redo log,主要改进fsync,使得效率更高,减少锁,优化flush机制,不会频繁flush。同时,支持更高用户并发请求。

    田帅萌
  • 腾讯云MySQL 8.0深度技术分析

    2020年7月8日,腾讯云正式发布了腾讯云MySQL 8.0。MySQL 8.0版本是一个里程碑式的版本,官方版本大幅度的提高了性能和可用性。而腾讯云基于官方版...

    用户1564362
  • 新特性解读 | MySQL 8.0 索引特性1-函数索引

    函数索引顾名思义就是加给字段加了函数的索引,这里的函数也可以是表达式。所以也叫表达式索引。

    田帅萌
  • Centos7安装MySQL8.0 - 操作手册

    MySQL 8 正式版 8.0.11 已发布,官方表示 MySQL 8 要比 MySQL 5.7 快 2 倍,还带来了大量的改进和更快的性能!

    洗尽了浮华
  • 360°全方位比较PostgreSQL和MySQL

    https://www.enterprisedb.com/blog/postgresql-vs-mysql-360-degree-comparison

    yzsDBA
  • 360°全方位比较PostgreSQL和MySQL

    https://www.enterprisedb.com/blog/postgresql-vs-mysql-360-degree-comparison

    yzsDBA
  • 技术新知

    1 MySQL8.0 pre-GA(pre General Avaliable:公共可用的先行版)于2018年3月19日发布,版本号为 8.0.12

    猿哥
  • 嗦一嗦 MySQL 8.0的新特性(一)

    导读 MySQL8.0 GA版本发布了,展现了众多新特性,本系列译文将整理为3篇,为大家介绍升级的部分新特性。 本文为第1篇,重点为大家介绍SQL、JSON上...

    wubx
  • AJAX

    XMLHttpRequest对象有一个onreadystatechange事件,可以监听这五个状态,它会在XMLHttpRequest对象的状态发生变化时被调用...

    py3study
  • MySQL5.7升级到8.0过程详解

    不知不觉,MySQL8.0已经发布好多个GA小版本了。目前互联网上也有很多关于MySQL8.0的内容了,MySQL8.0版本基本已到稳定期,相信很多小伙伴已经在...

    MySQL技术
  • PostgreSQL JSONB 使用入门

    Photo by Tobias Fischer[9] on Unsplash[10]

    goodspeed
  • 千呼万唤使出来——CSDN浏览器插件2.0彻底摆脱996

        前段时间在上班摸鱼的时候,偶然看到CSDN发布了2.0的插件版本,顿时眼睛一亮,作为CSDN的忠实粉丝,他的插件在1.0的时候就深度体验了一下还是很不错...

    XiaoLin_Java
  • Javaweb-案例练习-3-JSON对象

    前面一篇我们查找的图书的name都是通过逗号隔开,然后拼接成一个字符串传给浏览器。其实我们可以把多个图书名称用数组格式传给浏览器,前端JS代码通过遍历这个数组,...

    凯哥Java
  • Web-第十五天 Ajax学习【悟空教程】

    在实际开发中,完成注册功能前,如果用户填写用户信息,准备填写其他信息时,将提示当前用户的用户名是否可用。效果图如下:

    Java帮帮
  • vue 实时查询

    首先,我们来理解一下:节流函数首先是节流,就是节约流量、内存的损耗,旨在提升性能,在高频率频发的事件中才会用到,比如:onresize,onmousemove,...

    py3study
  • 【微服务】168:搜索的前端页面分析

    从上述代码可以看出是和top.js相关联的,也就是说要弄明白搜索相关的代码得去从top.js这个文件中找。

    刘小爱

扫码关注云+社区

领取腾讯云代金券