文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

使用 MySQL 8.0 操作 JSON 实践

2024-12-02 19:20

关注

经过漫长的测试,即将整体迁移至Mysql8.0; Mysql8.0 对于Json操作新增/优化了很多相关Json的API操作。

阅读了一下官方文档,虽然绝大多数的JSON操作都是应用层完成,但是会一些Mysql的JSON语法,方便进行debug;选出基础的, 有价值的部分,供未来参考。

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

简单概述

JSON基础工具 

  1. //使用JSON_ARRAY方法定义JSON数组; 
  2. SELECT JSON_ARRAY(1, "abc"NULLTRUE, CURTIME()) 
  3. //结果:[1, "abc"nulltrue"11:30:24.000000"]   
  4.  
  5. //JSON_OBJECT 方法定义JSON对象 
  6. SELECT JSON_OBJECT('id', 87, 'name''carrot'
  7. //结果{"id": 87, "name""carrot"
  8.  
  9. //数组 与 对象嵌套的场景; 
  10. [99, {"id""HK500""cost": 75.99}, ["hot""cold"]] {"k1""value""k2": [10, 20]} 
  11.  
  12. //日期/时间类型定义 
  13. ["12:18:29.000000""2015-07-29""2015-07-29 12:18:29.000000"
  14.  
  15.  //JSON_QUOTE 将JSON对象转义成String, 就是将内部的符  号进行转义,并整体包裹上双引号; 
  16. JSON_QUOTE(' "null" '
  17. //结果 "\"null\"" 
  18.  
  19. //将JSON内容美化并输出; 
  20. JSON_PRETTY() 
  21.  
  22. //可以将JSON/JSON内部的元素转化为其他数据类型; 
  23. //如下将JSON jdoc 中的id元素,转化为 unsigned int
  24. [https://dev.mysql.com/doc/refman/8.0/en/json.html#json-converting-between-types]    (https://dev.mysql.com/doc/refman/8.0/en/json.html#json-converting-between-types) 
  25. ORDER BY CAST(JSON_EXTRACT(jdoc, '$.id'AS   UNSIGNED); 

合并JSON的操作 JSON_MERGE_PRESERVE() and JSON_MERGE_PATCH() 实际业务用的可能性很少;-> -->操作符,按照key 找值;区别在于 -->会去除包裹的”以及转义符号; 它的等价的Function形式是JSON_EXTRACT() 。

  1. // {"mascot""Our mascot is a dolphin named \"Sakila\"."
  2. mysql> SELECT col->"$.mascot" FROM qtest; 
  3. //结果:| "Our mascot is a dolphin named \"Sakila\"." | 
  4. SELECT sentence->>"$.mascot" FROM facts; 
  5. // 结果: | Our mascot is a dolphin named "Sakila". | 

JSON Path expression上面 --> 后双引号中的内容就是所谓的JSON Path expression; 该语法是ECMAScript规范的一部分,所以前端程序员应该特别熟悉。

以下面这段JSON为例: 

  1. [3, {"a": [5, 6], "b": 10}, [99, 100]] 
  2. $[0] = 3 ; 
  3. $[1] = {"a": [5, 6], "b": 10}; 
  4. $[2] = [99, 100]; 

与此同时,[2] 并非标量, 进一步。 

  1. $[1].a = [5,6] 
  2. $[1].a[1] = 6 
  3. $[1].b = 10; 
  4. $[2][0] = 99; 

更进一步支持的语法特性$[n to m]

$[ 1 to 2] = [{"a": [5, 6], "b": 10}, [99, 100]] $[last-2 to last-1] = [3, {"a": [5, 6], "b": 10}]

总结一下;a .是代表所有的members in object; b []是代表所有的cells in array; c [prefix] ** suffix 是代表以prefix开始,以suffix为结束的所有路径;另外,MySQL 系列面试题和答案全部整理好了,微信搜索互联网架构师,在后台发送:2T,可以在线阅读。

查找并修改JSON 

  1. //如上, 应该可以用-->语法取代; 
  2. mysql> SELECT JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}''$.*'); 
  3. //[1, 2, [3, 4, 5]]   
  4. SELECT JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}''$.c[*]'
  5. //[3, 4, 5] 
  6. SELECT JSON_EXTRACT('{"a": {"b": 1}, "c": {"b": 2}}''$**.b'); 
  7. //[1, 2] 
  8. SELECT JSON_EXTRACT('[1, 2, 3, 4, 5]''$[1 to 3]'); 
  9. //[2, 3, 4] 
  10.  
  11. //JSON_SET JSON_INSERT JSON_REPLACE JSON_REMOVE 
  12. SET @j = '["a", {"b": [true, false]}, [10, 20]]'
  13. SELECT JSON_SET(@j, '$[1].b[0]', 1, '$[2][2]', 2); 
  14. //| ["a", {"b": [1, false]}, [10, 20, 2]]     
  15.  
  16. SELECT JSON_INSERT(@j, '$[1].b[0]', 1, '$[2][2]', 2); 
  17. //["a", {"b": [truefalse]}, [10, 20, 2]] 
  18.  
  19. JSON_REPLACE(@j, '$[1].b[0]', 1, '$[2][2]', 2) 
  20. //["a", {"b": [1, false]}, [10, 20]] 
  21.  
  22. SELECT JSON_REMOVE(@j, '$[2]''$[1].b[1]''$[1].b[1]'); 
  23. //["a", {"b": [true]}] 

JSON Table Functions 一个比较常见的场景是JSON数据本身是一个表的结构;JSON_TABLE(*expr*, *path* COLUMNS (*column_list*) [AS\] *alias*)。 

  1. SELECT *  FROM JSON_TABLE(   '[{"a":"3"},{"a":2},{"b":1},{"a":0},{"a":[1,2]}]'
  2.   ->     "$[*]" 
  3.   ->     COLUMNS( 
  4.   ->       rowid FOR ORDINALITY, 
  5.   ->       ac VARCHAR(100) PATH "$.a" DEFAULT '111' ON EMPTY DEFAULT '999' ON ERROR, 
  6.   ->       aj JSON PATH "$.a" DEFAULT '{"x": 333}' ON EMPTY, 
  7.   ->       bx INT EXISTS PATH "$.b" 
  8.   ->     ) 
  9.   ->   ) AS tt; 

 

 

来源:马哥Linux运维内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-后端开发
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯