MySQL JSON 学习笔记
##
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 authors\G *************************** 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 authors\G *************************** 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_object\G *************************** 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_array\G *************************** 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_object\G *************************** 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 movie\G *************************** 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_vendor\G *************************** 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)
未完待续！
免责声明：
1、本站资源由自动抓取工具收集整理于网络。
2、本站不承担由于内容的合法性及真实性所引起的一切争议和法律责任。
3、电子书、小说等仅供网友预览使用，书籍版权归作者或出版社所有。
4、如作者、出版社认为资源涉及侵权，请联系本站，本站将在收到通知书后尽快删除您认为侵权的作品。
5、如果您喜欢本资源，请您支持作者，购买正版内容。
6、资源失效，请下方留言，欢迎分享资源链接
文章评论