Mysql Json函数之搜索 (三)
本节中的函数对JSON值执行搜索操作,以从其中提取数据,报告数据是否在其中的某个位置或报告其中的数据的路径。
JSON_CONTAINS(
target
,candidate
[,path
])通过返回1或0指示给定的
candidate
JSON文档是否包含在target
JSON文档中,或者(如果提供了path
参数)指示是否 在目标内的特定路径上找到候选对象。返回NULL
是否有任何参数为NULL
,或者path参数没有标识目标文档的一部分。如果发生错误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 |
+-------------------------------+JSON_CONTAINS_PATH(
json_doc
,one_or_all
,path
[,path
] ...)返回0或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_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 |
+--------------------------------------------+
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及更高版本支持
->
该函数的快捷方式,与2个参数一起使用,其中左侧是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)(有关用于创建和填充刚刚显示的表的语句,请参见索引生成的列以提供JSON列索引。)
这也适用于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)支持嵌套数组。使用的表达式的
->
求值就像NULL
在目标JSON文档中找不到匹配的键一样,如下所示: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和更高版本中提供的一种改进的,取消引用的提取运算符。而
->
操作者简单地提取的值时,->>
在加法运算unquotes提取结果。换句话说,给定JSON
列值column
和路径表达式path
,以下三个表达式返回相同的值:JSON_UNQUOTE(
column
->
path
)column
->>path
->>
可以JSON_UNQUOTE(JSON_EXTRACT())
在允许的任何地方使用 该运算符 。这包括(但不限于)SELECT
列表,WHERE
和HAVING
条款,并ORDER 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)请参阅索引已生成的列以提供JSON列索引,以获取用于
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
如果任何参数是NULL
,则返回,该json_doc
参数不是对象,或者path
如果给定,则不定位对象。如果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_SEARCH(
json_doc
,one_or_all
,search_str
[,escape_char
[,path
] ...])返回JSON文档中给定字符串的路径。
NULL
如果任何一个,或 参数为json_doc
, 则 返回; 文件内不存在;或 找不到。如果参数不是有效的JSON文档,任何 参数不是有效的路径表达式, 不是 或或 不是常数表达式,都会发生错误。search_str
path
NULL
path
search_str
json_doc
path
one_or_all
'one'
'all'
escape_char
该
one_or_all
参数影响搜索,如下所示:'one'
:搜索在第一个匹配项后终止,并返回一个路径字符串。未定义首先考虑哪个匹配。'all'
:搜索将返回所有匹配的路径字符串,因此不包括重复的路径。如果有多个字符串,它们将自动包装为一个数组。数组元素的顺序是不确定的。
在
search_str
搜索字符串参数中,%
和_
字符与LIKE
运算符的作用相同:%
匹配任意数量的字符(包括零个字符),并且_
恰好匹配一个字符。要在搜索字符串中指定文字
%
或_
字符,请在其前面加上转义字符。默认值是\
,如果escape_char
参数丢失或NULL
。否则,escape_char
必须为空或一个字符的常量。有关匹配和转义字符行为的详细信息,请参阅的说明
LIKE
在 12.7.1节,“字符串比较函数和操作符”。对于转义字符处理,与LIKE
行为的区别 在于,转义字符JSON_SEARCH()
必须在编译时而不是仅在执行时求值为常数。例如,如果JSON_SEARCH()
在准备好的语句中escape_char
使用并且?
参数使用参数提供,则参数值在执行时可能是恒定的,但在编译时却不是。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路径语法。
最新文章
- Spark踩坑记——初试
- Javascript本质第二篇:执行上下文
- ListView遍历每个Item出现NullPointerException的异常
- 网页内嵌入QQ通信组件,唤起QQ,针对不同平台的处理方式
- udt nat traverse
- every、some、filter、map、forEach 方法的区别总结
- IIS日志路径,修改存放位置,清除日志方法
- Unity 粒子系统 特效 移除屏幕外面后再移回来 不会显示问题
- angular4.0如何引入外部插件1:import方案
- Windows下搭建Redis服务器
- IOS开发之XCode学习012:Slider和ProgressView
- python3-基础5
- Oracle不能连接故障排除[TNS-12541: TNS: 无监听程序]
- Nginx系列3:用Nginx搭建一个具备缓存功能的反向代理服务
- HTML5 学习07——Video(视频)Audio(音频)
- Jenkins和gitlab集成自动构建
- CDH:cdh5环境mkdir: Permission denied: user=root, access=WRITE, inode=";/user";:hdfs:hadoop:drwxr-xr-x
- python图片和字符串的转换
- Linux下 MYSQL 主从复制、同步
- 【UI测试】--易用性