12.18.3 查找JSON值的函数
源文档路径:源文档路径:MySQL :: MySQL 5.7 Reference Manual :: 12.18.3 Functions That Search JSON Values
这部分的函数是用来在JSON值上执行查找操作,以从它们中提取数据,报告数据是否存在于其中的某个位置,或者报告其中数据的路径。
通过返回1或0表示给定的candidate
JSON文档是否包含在target
JSON文档中,或者——如果提供了path
参数——返回candidate
是否在目标中的指定路径中。如果参数是NULL
则返回NULL
,或者如果路径参数未标识目标文档的某个部分。如果target
或者candidate
不是一个合法的JSON路径,或者如果path
参数不是一个合法的路径表达式,或者包含*
或者**
通配符,会出现错误。
要检查任何数据是否存在于路径中,使用JSON_CONTAINS_PATH()
来替代。
以下规则定义了控制:
-
当且仅当二者可比较且相等时,一个候选标量才被包含在目标标量中。如果两个标量值是同一种
JSON_TYPE()
类型,才是可比较的,例外的是INTEGER
和DECIMAL
类型也是相互可比较的。 -
当且仅当候选数组的每个元素都被包含在目标数组中的一些元素时,候选数组才被包含在目标数组中。
-
当且仅当候选非数组被包含在目标一些元素中时,一个候选非数组才被包含在目标数组中。
-
当且仅当对于候选中的每个键,目标中存在具有相同名称的键,并且与候选键相关联的值包含在与目标键相关联的值中时,候选对象包含在目标对象中。
否者,候选值不包含在目标文档中。
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 |
+-------------------------------+
返回1或者1,表示一个JSON文档在给定的一个路径或者多个路径中是否包含数据。如果参数是NULL
则返回NULL
。如果json_doc
参数不是一个合法的JSON文档,或者任何path
参数不是一个合法的路径表达式,或者one_or_all
不是one
或者all
。
要在一个路径检查指定的值,使用 JSON_CONTAINS()
来替代。
如果文档中不存在指定的路径,返回值是0。否则,返回值取决于one_or_all
参数。
'one'
:如果在文档中存在一个路径,返回1,否则返回0。'all'
:如果在文档中存在所有路径,返回1,否则返回0。
mysql> SET @j = '{"a": 1, "b": 2, "c": {"d": 4}}';
mysql> SELECT JSON_CONTAINS_PATH(@j, 'one', '$.a', '$.e');
+---------------------------------------------+
| JSON_CONTAINS_PATH(@j, 'one', '$.a', '$.e') |
+---------------------------------------------+
| 1 |
+---------------------------------------------+
mysql> SELECT JSON_CONTAINS_PATH(@j, 'all', '$.a', '$.e');
+---------------------------------------------+
| JSON_CONTAINS_PATH(@j, 'all', '$.a', '$.e') |
+---------------------------------------------+
| 0 |
+---------------------------------------------+
mysql> SELECT JSON_CONTAINS_PATH(@j, 'one', '$.c.d');
+----------------------------------------+
| JSON_CONTAINS_PATH(@j, 'one', '$.c.d') |
+----------------------------------------+
| 1 |
+----------------------------------------+
mysql> SELECT JSON_CONTAINS_PATH(@j, 'one', '$.a.d');
+----------------------------------------+
| JSON_CONTAINS_PATH(@j, 'one', '$.a.d') |
+----------------------------------------+
| 0 |
+----------------------------------------+
从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 |
+--------------------------------------------+
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 5.7.9和以后的版本支持->
操作符作为此方法的缩写,通过使用两个参数,左侧的参数是一个JSON
列标识符(不是一个表达式),右侧地参数是一个用来匹配列中数据的JSON路径。
在MySQL 5.7.9及以后,->
操作符在使用两个参数时,可作为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 | 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 |
+-------------------------------+-----------+------+
3 rows in set (0.00 sec)
这个功能不限制于SELECT
,如下所示:
mysql> ALTER TABLE jemp ADD COLUMN n INT;
Query OK, 0 rows affected (0.68 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> UPDATE jemp SET n=1 WHERE c->"$.id" = "4";
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT c, c->"$.id", g, n
> FROM jemp
> WHERE JSON_EXTRACT(c, "$.id") > 1
> ORDER BY c->"$.name";
+-------------------------------+-----------+------+------+
| c | c->"$.id" | g | n |
+-------------------------------+-----------+------+------+
| {"id": "3", "name": "Barney"} | "3" | 3 | NULL |
| {"id": "4", "name": "Betty"} | "4" | 4 | 1 |
| {"id": "2", "name": "Wilma"} | "2" | 2 | NULL |
+-------------------------------+-----------+------+------+
3 rows in set (0.00 sec)
mysql> DELETE FROM jemp WHERE c->"$.id" = "4";
Query OK, 1 row affected (0.04 sec)
mysql> SELECT c, c->"$.id", g, n
> FROM jemp
> WHERE JSON_EXTRACT(c, "$.id") > 1
> ORDER BY c->"$.name";
+-------------------------------+-----------+------+------+
| c | c->"$.id" | g | n |
+-------------------------------+-----------+------+------+
| {"id": "3", "name": "Barney"} | "3" | 3 | NULL |
| {"id": "2", "name": "Wilma"} | "2" | 2 | NULL |
+-------------------------------+-----------+------+------+
2 rows in set (0.00 sec)
(浏览Indexing a Generated Column to Provide a JSON Column Index,来获取用来创建和填充刚刚显示的表的语句。)
这也能操作JSON数组,如下所示:
mysql> CREATE TABLE tj10 (a JSON, b INT);
Query OK, 0 rows affected (0.26 sec)
mysql> INSERT INTO tj10
> VALUES ("[3,10,5,17,44]", 33), ("[3,10,5,17,[22,44,66]]", 0);
Query OK, 1 row affected (0.04 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)
同样支持内嵌的数组。如果在目标JSON文档中没有找到匹配的键,使用->
会得到NULL
,如下所示:
mysql> SELECT * FROM tj10 WHERE a->"$[4][1]" IS NOT NULL;
+------------------------------+------+
| a | b |
+------------------------------+------+
| [3, 10, 5, 17, [22, 44, 66]] | 0 |
+------------------------------+------+
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)
这是在MySQL 5.7.13及以后版本中可用的,升级版的不带引号的提取符。在->
操作符只是简单地提取值时,->>
操作符额外地去掉了提取结果的引号。换句话说,给定一个JSON
值column
和路径表达式path
(一个字符串文本),以下三个表达式会返回相同的值:
JSON_UNQUOTE(
JSON_EXTRACT(column
,path
) )- JSON_UNQUOTE(
column
->
path
) column
->>path
->>
操作符可以在任何支持JSON_UNQUOTE(JSON_EXTRACT())
的地方使用。这包括(但不限于)SELECT
列表,WHERE
和HAVING
子句,和ORDRE BY
和GROUP BY
子句。
下面几个语句展示了在mysql客户端中一些->>
操作符等同于其他表达式的示例:
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)
浏览Indexing a Generated Column to Provide a JSON Column Index,来获取刚刚用来创建和构造jemp
的一系列SQL语句。
这个操作符也可以与JSON数组使用,如下所示:
mysql> CREATE TABLE tj10 (a JSON, b INT);
Query OK, 0 rows affected (0.26 sec)
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.04 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SELECT a->"$[3]", a->"$[4][1]" FROM tj10;
+-----------+--------------+
| a->"$[3]" | a->"$[4][1]" |
+-----------+--------------+
| "x" | NULL |
| 17 | "y" |
+-----------+--------------+
2 rows in set (0.00 sec)
mysql> SELECT a->>"$[3]", a->>"$[4][1]" FROM tj10;
+------------+---------------+
| a->>"$[3]" | a->>"$[4][1]" |
+------------+---------------+
| x | NULL |
| 17 | y |
+------------+---------------+
2 rows in set (0.00 sec)
相对于->
,->>
操作符总会扩展EXPLAIN
的输出,如下面例子所示:
mysql> EXPLAIN SELECT c->>'$.name' AS name
-> FROM jemp WHERE g > 2\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: jemp
partitions: NULL
type: range
possible_keys: i
key: i
key_len: 5
ref: NULL
rows: 2
filtered: 100.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)
mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select
json_unquote(json_extract(`jtest`.`jemp`.`c`,'$.name')) AS `name` from
`jtest`.`jemp` where (`jtest`.`jemp`.`g` > 2)
1 row in set (0.00 sec)
这与MySQL在相同情况下扩展->
操作符的方式类似。
->>
操作符在MySQL 5.7.13中添加。
以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文档中给定字符串所在的路径。如果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
中操作符作用类似:%
匹配任意数量的字符(包括0字符),_
匹配一个确切的字符。
要在搜索字符串中指定一个%
或者_
字符的文本,在它之前加上转义字符。若escape_char
参数缺少或者为NULL
,则默认为\
。否则,escape_char
必须是常量,为空或者是一个字符。
要获取匹配和转义字符行为的更多信息,浏览Section 12.8.1, “String Comparison Functions and Operators”中LIKE
的描述。对于转义字符处理,与 LIKE
行为不同的一点是,JSON_SEARCH()
中的转义字符必须在编译时求值为一个常量,不是在执行时。比如,如果在一个预编译statement中使用JSON_SEARCH()
,且escape_char
参数通过使用一个?
参数提供,这个参数值应该在执行时就是一个常量,而不是编译时。
search_str
和path
总是以utf8mb4字符串插入,无论它们实际的编码为何。这是一个在MySQL 8.0中修复的著名的问题(Bug #32449181)。
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" |
+-------------------------------------------+
要获取关于MySQL支持的JSON路径语法的相关信息,包括管理*
和**
通配符的规则,请浏览JSON Path Syntax。