##
MySQL JSON 学习笔记
今日学习笔记!
首先我们创建一个包含json列的表
root@localhost [db01] (22:40:02) > create table book_authors(id int not null auto_increment,author json not null,primary key(id));
Query OK, 0 rows affected (0.02 sec)
1、插入值
json与其他类型没有什么不同,使用insert语句将值插入到表中.
set sql_mode='';
mysql>insert into book_authors values
(1,'{"id": 1, "name":"Paul","books":[
"Software portability with imake: Practical Software Engineering",
"MySQL: The Definitive Guide to Using, Programming,and Administering Mysql 4 (Developer's Library)",
"MySQL Certification Study Guide",
"MySOL (OTHER NEW RIDERS)",
"MySQL Cookbook",
"MySQL 5.0 Certification Study Guide",
"Using csh & tcsh:Type Less, Accomplish More (Nutshell Handbooks)",
"MySQL (Developer's Library)"],"lastname":"DuBois"}'),
(2,'{"id": 2,"name":"Alkin",
"books":["MySQL Cookbook"],"lastname":"Tezuysal"}'),
(3,'{"id":3,"name":"Sveta",
"books":["MySQL Troubleshooting","MySQL Cookbook"],
"lastname":"Smirnova"}');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
2、验证值是否为正确的json
使用json_valid函数,如果正确则返回1,如果错误则返回0.
root@localhost [db01] (08:18:23) > select json_valid('{"name":"tom"}');
+------------------------------+
| json_valid('{"name":"tom"}') |
+------------------------------+
| 1 |
+------------------------------+
1 row in set (0.00 sec)
root@localhost [db01] (08:19:55) > select json_valid('{"name":"tom}');
+-----------------------------+
| json_valid('{"name":"tom}') |
+-----------------------------+
| 0 |
+-----------------------------+
1 row in set (0.00 sec)
3、格式化json输出
默认查询的json值并不能很方便的阅读,我们需要使用json_pretty函数来格式化json列,以方便阅读。
root@localhost [db01] (08:26:23) > select author from book_authors where id=2;
+---------------------------------------------------------------------------------+
| author |
+---------------------------------------------------------------------------------+
| {"id": 2, "name": "Alkin", "books": ["MySQL Cookbook"], "lastname": "Tezuysal"} |
+---------------------------------------------------------------------------------+
1 row in set (0.00 sec)
root@localhost [db01] (08:26:28) > select json_pretty(author) from book_authors where id=2;
+---------------------------------------------------------------------------------------------------+
| json_pretty(author) |
+---------------------------------------------------------------------------------------------------+
| {
"id": 2,
"name": "Alkin",
"books": [
"MySQL Cookbook"
],
"lastname": "Tezuysal"
} |
+---------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
4、从json中提取值
json中的元素是由多个KV键值对组成,如果我们想要提取某一个kv的话,需要使用json_extract函数或者运算符->和->>.
json的跟元素符号表示$,对象成员由运算符.访问。数组成员由索引访问。通配符如果在点后使用,则为所有对象成员的值.;如果用括号括起来[*]则为所有数组元素,[prefix]**suffix该表达式表示所有路径,以prefix开头并以suffix结尾,suffix是必须的,但prefix是可选的,换句话说,json路径的表达式不应以双星号结尾。
root@localhost [db01] (08:26:31) > select json_extract(author,'$.name') as author from book_authors;
+---------+
| author |
+---------+
| "Paul" |
| "Alkin" |
| "Sveta" |
+---------+
3 rows in set (0.00 sec)
如果要从值中删除引号,需要使用json_unquote函数
root@localhost [db01] (08:35:16) > select json_unquote(json_extract(author,'$.name')) as author from book_authors;
+--------+
| author |
+--------+
| Paul |
| Alkin |
| Sveta |
+--------+
3 rows in set (0.01 sec)
运算符->是函数json_extract的别名。
root@localhost [db01] (08:38:02) > select author -> '$.name' as author from book_authors;
+---------+
| author |
+---------+
| "Paul" |
| "Alkin" |
| "Sveta" |
+---------+
3 rows in set (0.00 sec)
运算符->>是函数json_unquote(json_extract(…))的别名。
root@localhost [db01] (08:38:09) > select author ->> '$.name' as author from book_authors;
+--------+
| author |
+--------+
| Paul |
| Alkin |
| Sveta |
+--------+
3 rows in set (0.00 sec)
要提取作者的第一本书和最后一本书,请分别使用0和last数组索引:
root@localhost [db01] (08:45:19) > select concat(author->>'$.name',' ',author->>'$.lastname') as author,author->>'$.books[0]' as `First Book`,author->>'$.books[last]' as `Last Book` from book_authors;
+----------------+-----------------------------------------------------------------+-----------------------------+
| author | First Book | Last Book |
+----------------+-----------------------------------------------------------------+-----------------------------+
| Paul DuBois | Software portability with imake: Practical Software Engineering | MySQL (Developer's Library) |
| Alkin Tezuysal | MySQL Cookbook | MySQL Cookbook |
| Sveta Smirnova | MySQL Troubleshooting | MySQL Cookbook |
+----------------+-----------------------------------------------------------------+-----------------------------+
3 rows in set (0.00 sec)
也可以使用$.[3 to 5]
通配符*
root@localhost [db01] (08:55:50) > select author->>'$.*' from book_authors;
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| author->>'$.*' |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| [1, "Paul", ["Software portability with imake: Practical Software Engineering", "MySQL: The Definitive Guide to Using, Programming,and Administering Mysql 4 (Developer's Library)", "MySQL Certification Study Guide", "MySOL (OTHER NEW RIDERS)", "MySQL Cookbook", "MySQL 5.0 Certification Study Guide", "Using csh & tcsh:Type Less, Accomplish More (Nutshell Handbooks)", "MySQL (Developer's Library)"], "DuBois"] |
| [2, "Alkin", ["MySQL Cookbook"], "Tezuysal"] |
| [3, "Sveta", ["MySQL Troubleshooting", "MySQL Cookbook"], "Smirnova"] |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
使用json作为where条件查询
root@localhost [db01] (08:58:57) > select author->>'$.*' from book_authors where author->>'$.name'='Sveta';
+-----------------------------------------------------------------------+
| author->>'$.*' |
+-----------------------------------------------------------------------+
| [3, "Sveta", ["MySQL Troubleshooting", "MySQL Cookbook"], "Smirnova"] |
+-----------------------------------------------------------------------+
1 row in set (0.00 sec)
5、在json中进行搜索
如果想要再json中搜索特定的值,需要使用json_search功能。
root@localhost [db01] (09:13:53) > select author->>'$.name' as author from book_authors where json_search(author,'one','MySQL Cookbook');
+--------+
| author |
+--------+
| Paul |
| Alkin |
| Sveta |
+--------+
3 rows in set, 1 warning (0.00 sec)
json_search支持%和_通配符。
root@localhost [db01] (09:16:52) > select author->>'$.name' as author from book_authors where json_search(author,'one','Sv%');
+--------+
| author |
+--------+
| Sveta |
+--------+
1 row in set, 1 warning (0.00 sec)
root@localhost [db01] (09:18:26) > select author->>'$.name' as author from book_authors where json_search(author,'one','Sv_');
Empty set, 1 warning (0.01 sec)
one和all案例
如果是 ‘one’,JSON_SEARCH() 函数将返回第一个匹配的路径。
如果是 ‘all’,JSON_SEARCH() 函数将返回所有匹配的路径。所有的路径会包装在一个数组内返回。
root@localhost [db01] (09:24:04) > select author->>'$.name' as author,json_search(author,'one','MySQL%') as books from book_authors;
+--------+--------------+
| author | books |
+--------+--------------+
| Paul | "$.books[1]" |
| Alkin | "$.books[0]" |
| Sveta | "$.books[0]" |
+--------+--------------+
3 rows in set (0.00 sec)
root@localhost [db01] (09:25:04) > select author->>'$.name' as author,json_search(author,'all','MySQL%') as books from book_authors;
+--------+------------------------------------------------------------------------+
| author | books |
+--------+------------------------------------------------------------------------+
| Paul | ["$.books[1]", "$.books[2]", "$.books[4]", "$.books[5]", "$.books[7]"] |
| Alkin | "$.books[0]" |
| Sveta | ["$.books[0]", "$.books[1]"] |
+--------+------------------------------------------------------------------------+
3 rows in set (0.00 sec)
搜索单个匹配项时,可以使用json_search函数的返回值作为json_extract函数的参数:
root@localhost [db01] (09:29:49) > select author->>'$.name' as author,json_extract(author,json_unquote(json_search(author,'one','MySQL%')))
+--------+-----------------------------------------------------------------------------------------------------+
| author | books |
+--------+-----------------------------------------------------------------------------------------------------+
| Paul | "MySQL: The Definitive Guide to Using, Programming,and Administering Mysql 4 (Developer's Library)" |
| Alkin | "MySQL Cookbook" |
| Sveta | "MySQL Troubleshooting" |
+--------+-----------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
6、将新元素插入json
这里需要使用json_insert、json_array_append和json_array_insert函数。
mysql支持追加,删除和替换json的内容,以及将两个或对个文档合并为一个。
将新值插入到json对象中案例:
root@localhost [db01] (09:40:22) > update book_authors set author=json_insert(author,'$.work','Percona') where author->>'$.name' in ('Sveta','Alkin');
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
root@localhost [db01] (09:40:27) > select author->>'$.work' from book_authors;
+-------------------+
| author->>'$.work' |
+-------------------+
| NULL |
| Percona |
| Percona |
+-------------------+
3 rows in set (0.00 sec)
将一本书添加到book的末尾,需要使用json_array_append函数
root@localhost [db01] (09:41:13) > update book_authors set author=json_array_append(author,'$.books','MySQL Performance Schema in Action') where author->>'$.name' = 'Sveta';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
root@localhost [db01] (09:45:10) > select author->>'$.books' from book_authors where author->>'$.name'='Sveta';
+-----------------------------------------------------------------------------------+
| author->>'$.books' |
+-----------------------------------------------------------------------------------+
| ["MySQL Troubleshooting", "MySQL Cookbook", "MySQL Performance Schema in Action"] |
+-----------------------------------------------------------------------------------+
1 row in set (0.00 sec)
root@localhost [db01] (09:46:09) >
要将元素添加到指定的位置,需要使用json_array_insert函数。
root@localhost [db01] (09:46:09) > update book_authors set author=json_array_insert(author,'$.books[0]','MySQL for Absolute Beginners') where author->>'$.name' = 'Alkin';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
root@localhost [db01] (09:49:29) > select author->>'$.books' from book_authors where author->>'$.name'='Alkin';
+----------------------------------------------------+
| author->>'$.books' |
+----------------------------------------------------+
| ["MySQL for Absolute Beginners", "MySQL Cookbook"] |
+----------------------------------------------------+
1 row in set (0.00 sec)
root@localhost [db01] (09:49:46) >
7、更新json
使用json_replace和json_set函数
json_replace函数案例:
root@localhost [db01] (09:56:54) > update book_authors set author=json_replace(author,'$.work','EDB') where author->>'$.name'='Alkin';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
root@localhost [db01] (10:00:16) > select author->>'$.work' as work from book_authors where author->>'$.name'='Alkin';
+------+
| work |
+------+
| EDB |
+------+
1 row in set (0.01 sec)
但是,如果文档中不存在需要替换的记录,该函数不执行任何操作。
root@localhost [db01] (10:00:51) > select author->>'$.work' as work from book_authors where author->>'$.name'='Paul';
+------+
| work |
+------+
| NULL |
+------+
1 row in set (0.01 sec)
root@localhost [db01] (10:02:14) > update book_authors set author=json_replace(author,'$.work','Oracle') where author->>'$.name'='Paul'
;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
root@localhost [db01] (10:02:35) > select author->>'$.work' as work from book_authors where author->>'$.name'='Paul';
+------+
| work |
+------+
| NULL |
+------+
1 row in set (0.00 sec)
要解决此问题,需要使用json_set函数,在路径存在时更新文档,或者路径不存在是插入新值。
root@localhost [db01] (10:02:37) > update book_authors set author=json_set(author,'$.work','Oracle') where author->>'$.name'='Paul'; Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
root@localhost [db01] (10:09:01) > select author->>'$.work' as work from book_authors where author->>'$.name'='Paul';
+--------+
| work |
+--------+
| Oracle |
+--------+
1 row in set (0.00 sec)
root@localhost [db01] (10:09:03) > update book_authors set author=json_set(author,'$.work','MySQL') where author->>'$.name'='Paul';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
root@localhost [db01] (10:09:13) > select author->>'$.work' as work from book_authors where author->>'$.name'='Paul';
+-------+
| work |
+-------+
| MySQL |
+-------+
1 row in set (0.00 sec)
8、从json中删除元素
需要使用json_remove功能,该函数从json中删除指定的元素。
root@localhost [db01] (10:09:15) > update book_authors set author=json_remove(author,'$.book[0]') where author->>'$.name'='Alkin';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
root@localhost [db01] (10:22:17) > update book_authors set author=json_remove(author,'$.book[last]') where author->>'$.name'='Sveta';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
9、将两个或多个json文档合并成为一个
需要使用json_merge_*函数(json_merge_patch和json_merge_preserve)。json_merge_patch合并两个文档时删除重复项,而json_merge_preserve保留他们,这两个函数 都接受两个或多个参数。
先准备一些变量以供测试:
root@localhost [db01] (14:27:38) > select author into @paul from book_authors where author->>'$.name'='Paul';
Query OK, 1 row affected (0.00 sec)
root@localhost [db01] (14:27:46) > select author into @sveta from book_authors where author->>'$.name'='Sveta';
Query OK, 1 row affected (0.00 sec)
root@localhost [db01] (14:28:01) > select author into @alkin from book_authors where author->>'$.name'='Alkin';
Query OK, 1 row affected (0.00 sec)
root@localhost [db01] (14:28:18) > select author->>'$.books' into @sveta_books from book_authors where author->>'$.name'='Sveta';
Query OK, 1 row affected (0.00 sec)
json_merge_preserve将其参数提供的文档组合成一个对象,可以使用此函数向对象或数组添加新元素.
root@localhost [db01] (14:29:03) > select json_pretty(json_merge_preserve(@sveta,'{"places lived":["Russia","Turkey"]}'))G
*************************** 1. row ***************************
json_pretty(json_merge_preserve(@sveta,'{"places lived":["Russia","Turkey"]}')): {
"id": 3,
"name": "Sveta",
"work": "Percona",
"books": [
"MySQL Troubleshooting",
"MySQL Cookbook",
"MySQL Performance Schema in Action"
],
"lastname": "Smirnova",
"places lived": [
"Russia",
"Turkey"
]
}
1 row in set (0.00 sec)
root@localhost [db01] (14:32:00) > select json_pretty(json_merge_preserve(@sveta,'{"books":["MySQL Performance Schema in Action"]}'))G
*************************** 1. row ***************************
json_pretty(json_merge_preserve(@sveta,'{"books":["MySQL Performance Schema in Action"]}')): {
"id": 3,
"name": "Sveta",
"work": "Percona",
"books": [
"MySQL Troubleshooting",
"MySQL Cookbook",
"MySQL Performance Schema in Action",
"MySQL Performance Schema in Action"
],
"lastname": "Smirnova"
}
1 row in set (0.00 sec)
该函数json_merge_preserve不会处理重复项,如果两个对象具有相同键的标量值,他们将被合并到一个数组中
root@localhost [db01] (14:36:10) > select json_pretty(json_merge_preserve(@paul,@sveta,@alkin)) as authorsG
*************************** 1. row ***************************
authors: {
"id": [
1,
3,
2
],
"name": [
"Paul",
"Sveta",
"Alkin"
],
"work": [
"MySQL",
"Percona",
"EDB"
],
"books": [
"Software portability with imake: Practical Software Engineering",
"MySQL: The Definitive Guide to Using, Programming,and Administering Mysql 4 (Developer's Library)",
"MySQL Certification Study Guide",
"MySOL (OTHER NEW RIDERS)",
"MySQL Cookbook",
"MySQL 5.0 Certification Study Guide",
"Using csh & tcsh:Type Less, Accomplish More (Nutshell Handbooks)",
"MySQL (Developer's Library)",
"MySQL Troubleshooting",
"MySQL Cookbook",
"MySQL Performance Schema in Action",
"MySQL for Absolute Beginners",
"MySQL Cookbook"
],
"lastname": [
"DuBois",
"Smirnova",
"Tezuysal"
]
}
1 row in set (0.00 sec)
相反json_merge_patch函数会删除重复项,合并相同值返回指定的最后一个参数的一位。
root@localhost [db01] (14:43:13) > select json_pretty(json_merge_patch(@paul,@sveta,@alkin)) as authorsG *************************** 1. row ***************************
authors: {
"id": 2,
"name": "Alkin",
"work": "EDB",
"books": [
"MySQL for Absolute Beginners",
"MySQL Cookbook"
],
"lastname": "Tezuysal"
}
1 row in set (0.00 sec)
此功能用于从json中删除不需要的元素。利用null元素。
root@localhost [db01] (14:47:11) > select json_pretty(@sveta)G
*************************** 1. row ***************************
json_pretty(@sveta): {
"id": 3,
"name": "Sveta",
"work": "Percona",
"books": [
"MySQL Troubleshooting",
"MySQL Cookbook",
"MySQL Performance Schema in Action"
],
"lastname": "Smirnova"
}
1 row in set (0.00 sec)
root@localhost [db01] (14:43:43) > select json_pretty(json_merge_patch(@sveta,'{"work":null}'))G
*************************** 1. row ***************************
json_pretty(json_merge_patch(@sveta,'{"work":null}')): {
"id": 3,
"name": "Sveta",
"books": [
"MySQL Troubleshooting",
"MySQL Cookbook",
"MySQL Performance Schema in Action"
],
"lastname": "Smirnova"
}
1 row in set (0.00 sec)
当函数的最新文档不是对象时,json_merge_preserve会将其添加为数组的最新元素,而json_merge_patch会将文档中的元素替换为新文档中的元素.
root@localhost [db01] (14:56:58) > select json_pretty(@sveta_books)G
*************************** 1. row ***************************
json_pretty(@sveta_books): [
"MySQL Troubleshooting",
"MySQL Cookbook",
"MySQL Performance Schema in Action"
]
1 row in set (0.00 sec)
root@localhost [db01] (14:56:31) > select json_pretty(json_merge_patch(@sveta_books,'"MySQL Performance Schema in Action"')) as 'Books by Sveta';
+--------------------------------------+
| Books by Sveta |
+--------------------------------------+
| "MySQL Performance Schema in Action" |
+--------------------------------------+
1 row in set (0.00 sec)
root@localhost [db01] (14:56:44) > select json_pretty(json_merge_preserve(@sveta_books,'"MySQL Performance Sch
ema in Action"')) as 'Books by Sveta';
+-----------------------------------------------------------------------------------------------------------------------------------+
| Books by Sveta |
+-----------------------------------------------------------------------------------------------------------------------------------+
| [
"MySQL Troubleshooting",
"MySQL Cookbook",
"MySQL Performance Schema in Action",
"MySQL Performance Schema in Action"
] |
+-----------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
10、从关系数据创建json
如果希望从关系数据创建json,那么需要使用json_object和json_array函数,以及聚合变体json_objectagg和json_arrayagg。
10.1、json_object函数
该函数可以将值对组合成json对象。
root@localhost [db01] (16:12:59) > select json_pretty(json_object("string","Some String","number",42,"null",null)) as my_objectG
*************************** 1. row ***************************
my_object: {
"null": null,
"number": 42,
"string": "Some String"
}
1 row in set (0.00 sec)
10.2、json_array函数
该函数可以将参数组合成json数组。
root@localhost [db01] (16:13:13) > select json_pretty(json_array("one","two","three",4,5)) as my_arrayG *************************** 1. row ***************************
my_array: [
"one",
"two",
"three",
4,
5
]
1 row in set (0.00 sec)
使用两个函数的创建嵌套对象和数组
root@localhost [db01] (16:19:53) > select json_pretty(json_object("Example","Nesting object and array","Human",json_object("name","Sveta","lastname","Smirnova"),"Numbers",json_array("one","two","three"))) AS my_objectG
*************************** 1. row ***************************
my_object: {
"Human": {
"name": "Sveta",
"lastname": "Smirnova"
},
"Example": "Nesting object and array",
"Numbers": [
"one",
"two",
"three"
]
}
1 row in set (0.00 sec)
10.3、json_arrayagg函数
让json列出一部电影以及该电影的演员
root@localhost [db01] (16:50:44) > select * from movies_actors group by movie;
+------+----------------------------+---------------+
| year | movie | actor |
+------+----------------------------+---------------+
| 2005 | Kingdom of Heaven | Liam Neeson |
| 1997 | The Fifth Element | Bruce Willis |
| 1999 | The Phantom Menace | Ewan McGregor |
| 2001 | The Fellowship of the Ring | Ian McKellen |
| 2010 | Red | Helen Mirren |
| 2011 | Unknown | Diane Kruger |
+------+----------------------------+---------------+
6 rows in set (0.01 sec)
root@localhost [db01] (16:41:48) > select json_pretty(json_object('Movie',movie,'Starred',json_arrayagg(actor))) as starred from movies_actors group by movieG
*************************** 1. row ***************************
starred: {
"Movie": "Kingdom of Heaven",
"Starred": [
"Liam Neeson",
"Orlando Bloom"
]
}
*************************** 2. row ***************************
starred: {
"Movie": "Red",
"Starred": [
"Helen Mirren",
"Bruce Willis"
]
}
*************************** 3. row ***************************
starred: {
"Movie": "The Fellowship of the Ring",
"Starred": [
"Ian McKellen",
"Ian Holm",
"Orlando Bloom",
"Elijah Wood"
]
}
*************************** 4. row ***************************
starred: {
"Movie": "The Fifth Element",
"Starred": [
"Bruce Willis",
"Gary Oldman",
"Ian Holm"
]
}
*************************** 5. row ***************************
starred: {
"Movie": "The Phantom Menace",
"Starred": [
"Ewan McGregor",
"Liam Neeson"
]
}
*************************** 6. row ***************************
starred: {
"Movie": "Unknown",
"Starred": [
"Diane Kruger",
"Liam Neeson"
]
}
6 rows in set (0.00 sec)
10.4、json_objectagg函数
该函数可以将一列中的表值作为成员列表,并将另一列中的值作为其参数
root@localhost [db01] (16:49:27) > select json_pretty(json_objectagg(name,website)) as websites from book_vendorG
*************************** 1. row ***************************
websites: {
"Amazon.com": "www.amazon.com",
"Barnes & Noble": "www.barnesandnoble.com",
"O'Reilly Media": "www.oreilly.com"
}
1 row in set (0.00 sec)
root@localhost [db01] (16:49:42) > select * from book_vendor;
+----------------+------------------------+
| name | website |
+----------------+------------------------+
| Amazon.com | www.amazon.com |
| Barnes & Noble | www.barnesandnoble.com |
| O'Reilly Media | www.oreilly.com |
+----------------+------------------------+
3 rows in set (0.00 sec)
未完待续!