MySQL的函数和运算符 - JSON 函数 - JSON值搜索函数（2）

林员外聊编程 2021-09-04

256

● 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"]                            |
+---------------------------------------+
mysql> SELECT JSON_KEYS('{"a": 1, "b": {"c": 30}}', '$.b');
+----------------------------------------------+
| JSON_KEYS('{"a": 1, "b": {"c": 30}}', '$.b') |
+----------------------------------------------+
| ["c"]                                        |
+----------------------------------------------+
复制

● JSON_OVERLAPS(json_doc1, json_doc2)

比较两个JSON文档。如果两个文档有任何共同的键值对或数组元素，则返回 true (1)。如果两个参数都是标量，则函数执行一个简单的相等测试。

这个函数与 JSON_CONTAINS() 相对应，后者要求搜索的数组的所有元素都出现在搜索的数组中。因此，JSON_CONTAINS() 对搜索键执行 AND 操作，而 JSON_OVERLAPS() 执行 OR 操作。

在 WHERE 子句中使用 JSON_OVERLAPS() 查询 InnoDB 表的 JSON 列可以使用多值索引进行优化。

当比较两个数组时，如果它们共享一个或多个数组元素，JSON_OVERLAPS() 返回 true，否则返回 false：

mysql> SELECT JSON_OVERLAPS("[1,3,5,7]", "[2,5,7]");
+---------------------------------------+
| JSON_OVERLAPS("[1,3,5,7]", "[2,5,7]") |
+---------------------------------------+
|                                     1 |
+---------------------------------------+
1 row in set (0.00 sec)


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('[[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> 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> SELECT JSON_OVERLAPS('[4,5,6,7]', '"6"');
+-----------------------------------+
| JSON_OVERLAPS('[4,5,6,7]', '"6"') |
+-----------------------------------+
|                                 0 |
+-----------------------------------+
1 row in set (0.00 sec)
复制

JSON_OVERLAPS() 从 MySQL 8.0.17 中开始添加。

● JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...])

返回 JSON 文档中给定字符串的路径。如果 json_doc, search_str 或 path 参数为 NULL，或者文档中不存在给定 path，或者找不到 search_str，则返回 NULL。如果 json_doc 参数不是一个有效的 JSON 文档，某个 path 参数不是一个有效的路径表达式，one_or_all 不是 'one' 或 'all'，或者 escape_char 不是一个常量表达式，就会发生错误。

参数 one_or_all 对搜索的影响如下：

■ 'one': 搜索在第一个匹配后结束，并返回一个路径字符串。没有定义先考虑哪个匹配。

■ 'all': 搜索返回所有匹配的路径字符串，这样就不会包含重复的路径。如果有多个字符串，它们将自动包装为数组。数组元素的顺序未定义。

在 search_str 搜索字符串参数中，% 和 _ 字符的工作方式与他们在 LIKE 操作符中一样：% 匹配任意数量的字符(包括零个字符)，而 _ 只匹配一个字符。

若要在搜索字符串中指定文本 % 或 _ 字符字面量，请在其前面加上转义字符。如果 escape_char 参数缺失或为 NULL，则默认值为 \。否则，escape_char 必须是一个空的常量或一个字符。

对于转义字符处理，与 LIKE 行为的不同之处在于 JSON_SEARCH() 的转义字符必须在编译时计算为常量，而不仅是在执行时。例如，如果在预编译语句中使用 JSON_SEARCH()，并且 escape_char 参数使用 ? 参数替代时，参数值在执行时可能是常量，但在编译时不是。

search_str 和 path 总是被解释为 utf8mb4 字符串，而不管它们的实际编码。这是一个已知的问题，在 MySQL 8.0.24 已修复。

mysql> SET @j = '["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]';


mysql> SELECT JSON_SEARCH(@j, 'one', 'abc');
+-------------------------------+
| JSON_SEARCH(@j, 'one', 'abc') |
+-------------------------------+
| "$[0]"                        |
+-------------------------------+


mysql> SELECT JSON_SEARCH(@j, 'all', 'abc');
+-------------------------------+
| JSON_SEARCH(@j, 'all', 'abc') |
+-------------------------------+
| ["$[0]", "$[2].x"]            |
+-------------------------------+


mysql> SELECT JSON_SEARCH(@j, 'all', 'ghi');
+-------------------------------+
| JSON_SEARCH(@j, 'all', 'ghi') |
+-------------------------------+
| NULL                          |
+-------------------------------+


mysql> SELECT JSON_SEARCH(@j, 'all', '10');
+------------------------------+
| JSON_SEARCH(@j, 'all', '10') |
+------------------------------+
| "$[1][0].k"                  |
+------------------------------+


mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$');
+-----------------------------------------+
| JSON_SEARCH(@j, 'all', '10', NULL, '$') |
+-----------------------------------------+
| "$[1][0].k"                             |
+-----------------------------------------+


mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$[*]');
+--------------------------------------------+
| JSON_SEARCH(@j, 'all', '10', NULL, '$[*]') |
+--------------------------------------------+
| "$[1][0].k"                                |
+--------------------------------------------+


mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$**.k');
+---------------------------------------------+
| JSON_SEARCH(@j, 'all', '10', NULL, '$**.k') |
+---------------------------------------------+
| "$[1][0].k"                                 |
+---------------------------------------------+


mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$[*][0].k');
+-------------------------------------------------+
| JSON_SEARCH(@j, 'all', '10', NULL, '$[*][0].k') |
+-------------------------------------------------+
| "$[1][0].k"                                     |
+-------------------------------------------------+


mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$[1]');
+--------------------------------------------+
| JSON_SEARCH(@j, 'all', '10', NULL, '$[1]') |
+--------------------------------------------+
| "$[1][0].k"                                |
+--------------------------------------------+


mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$[1][0]');
+-----------------------------------------------+
| JSON_SEARCH(@j, 'all', '10', NULL, '$[1][0]') |
+-----------------------------------------------+
| "$[1][0].k"                                   |
+-----------------------------------------------+


mysql> SELECT JSON_SEARCH(@j, 'all', 'abc', NULL, '$[2]');
+---------------------------------------------+
| JSON_SEARCH(@j, 'all', 'abc', NULL, '$[2]') |
+---------------------------------------------+
| "$[2].x"                                    |
+---------------------------------------------+


mysql> SELECT JSON_SEARCH(@j, 'all', '%a%');
+-------------------------------+
| JSON_SEARCH(@j, 'all', '%a%') |
+-------------------------------+
| ["$[0]", "$[2].x"]            |
+-------------------------------+


mysql> SELECT JSON_SEARCH(@j, 'all', '%b%');
+-------------------------------+
| JSON_SEARCH(@j, 'all', '%b%') |
+-------------------------------+
| ["$[0]", "$[2].x", "$[3].y"]  |
+-------------------------------+


mysql> SELECT JSON_SEARCH(@j, 'all', '%b%', NULL, '$[0]');
+---------------------------------------------+
| JSON_SEARCH(@j, 'all', '%b%', NULL, '$[0]') |
+---------------------------------------------+
| "$[0]"                                      |
+---------------------------------------------+


mysql> SELECT JSON_SEARCH(@j, 'all', '%b%', NULL, '$[2]');
+---------------------------------------------+
| JSON_SEARCH(@j, 'all', '%b%', NULL, '$[2]') |
+---------------------------------------------+
| "$[2].x"                                    |
+---------------------------------------------+


mysql> SELECT JSON_SEARCH(@j, 'all', '%b%', NULL, '$[1]');
+---------------------------------------------+
| JSON_SEARCH(@j, 'all', '%b%', NULL, '$[1]') |
+---------------------------------------------+
| NULL                                        |
+---------------------------------------------+


mysql> SELECT JSON_SEARCH(@j, 'all', '%b%', '', '$[1]');
+-------------------------------------------+
| JSON_SEARCH(@j, 'all', '%b%', '', '$[1]') |
+-------------------------------------------+
| NULL                                      |
+-------------------------------------------+


mysql> SELECT JSON_SEARCH(@j, 'all', '%b%', '', '$[3]');
+-------------------------------------------+
| JSON_SEARCH(@j, 'all', '%b%', '', '$[3]') |
+-------------------------------------------+
| "$[3].y"                                  |
+-------------------------------------------+
复制

官方网址：

https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html

mysql

文章转载自林员外聊编程，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MySQL的函数和运算符 - JSON 函数 - JSON值搜索函数（2）

评论

相关阅读