尝鲜!Mysql8.0竟然可以直接操作json文档了-阿里云开发者社区

开发者社区> dasein58> 正文

尝鲜!Mysql8.0竟然可以直接操作json文档了

简介:   经过漫长的测试,即将整体迁移至Mysql8.0; Mysql8.0 对于Json操作新增/优化了很多相关Json的API操作; 阅读了一下官方文档,虽然绝大多数的JSON操作都是应用层完成,但是会一些Mysql的JSON语法,方便进行debug;选出基础的, 有价值的部分,供未来参考。   # 简单概述   不允许为null; Json格式定义与LONGBLOB or LONGTEXT类似;它的最大长度是受到max_allowed_packet所控制的;查看JSON字段所占用空间大小的函数时JSON_STORAGE_SIZE(xxx);除普通的Json操作,额外支持GeoJSON (
+关注继续查看

  经过漫长的测试,即将整体迁移至Mysql8.0; Mysql8.0 对于Json操作新增/优化了很多相关Json的API操作; 阅读了一下官方文档,虽然绝大多数的JSON操作都是应用层完成,但是会一些Mysql的JSON语法,方便进行debug;选出基础的, 有价值的部分,供未来参考。

  # 简单概述

  不允许为null; Json格式定义与LONGBLOB or LONGTEXT类似;它的最大长度是受到max_allowed_packet所控制的;查看JSON字段所占用空间大小的函数时JSON_STORAGE_SIZE(xxx);除普通的Json操作,额外支持GeoJSON (基于几何图形的针对地理空间数据交换格式)一些相关操作;对Json栏位支持索引(结合Mysql8.0新特性,函数index);一个可以支持部分的,原地更新Json Column 的可选优化项加入MySql8.0; 可以使用的函数有JSON_SET(), JSON_REPLACE() ,JSON_REMOVE(); 使用时,有一些约束,但是会有更加的性能;JSON基础工具;

  //使用JSON_ARRAY方法定义JSON数组;SELECT JSON_ARRAY(1, "abc", NULL, TRUE, CURTIME())

  //结果:[1, "abc", null, true, "11:30:24.000000"]

  //JSON_OBJECT 方法定义JSON对象

  SELECT JSON_OBJECT('id', 87, 'name', 'carrot')

  //结果{"id": 87, "name": "carrot"}

  //数组 与 对象嵌套的场景;

  [99, {"id": "HK500", "cost": 75.99}, ["hot", "cold"]] {"k1": "value", "k2": [10, 20]}

  //日期/时间类型定义

  ["12:18:29.000000", "2021-07-29", "2021-07-29 12:18:29.000000"]

  //JSON_QUOTE 将JSON对象转义成String, 就是将内部的符 号进行转义,并整体包裹上双引号;

  JSON_QUOTE(' "null" ')

  //结果 ""null\""

  //将JSON内容美化并输出;

  JSON_PRETTY()

  //可以将JSON/JSON内部的元素转化为其他数据类型;

  //如下将JSON jdoc 中的id元素,转化为 unsigned int;

  [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)

  ORDER BY CAST(JSON_EXTRACT(jdoc, '$.id') AS UNSIGNED);合并JSON的操作 JSON_MERGE_PRESERVE() and JSON_MERGE_PATCH() 实际业务用的可能性很少;-> -->操作符,按照key 找值;区别在于 -->会去除包裹的”以及转义符号; 它的二手购买等价的Function形式是JSON_EXTRACT()

  // {"mascot": "Our mascot is a dolphin named "Sakila"."}

  mysql> SELECT col->"$.mascot" FROM qtest;

  //结果:| "Our mascot is a dolphin named "Sakila"." |

  SELECT sentence->>"$.mascot" FROM facts;

  // 结果:| Our mascot is a dolphin named "Sakila".JSON Path expression

  上面 --> 后双引号中的内容就是所谓的JSON Path expression;

  该语法是ECMAScript规范的一部分,所以前端程序员应该特别熟悉;

  以下面这段JSON为例;

  [3, {"a": [5, 6], "b": 10}, [99, 100]]

  $[0]=3 ;

  $[1]={"a": [5, 6], "b": 10};

  $[2]=[99, 100];

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

  $[1].a=[5,6]

  $[1].a[1]=6

  $[1].b=10;

  $2=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为结束的所有路径;

  查找并修改JSON

  //如上, 应该可以用-->语法取代;

  mysql> SELECT JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}', '$.*');

  //[1, 2, [3, 4, 5]]

  SELECT JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}', '$.c[*]')

  //[3, 4, 5]

  SELECT JSON_EXTRACT('{"a": {"b": 1}, "c": {"b": 2}}', '$**.b');

  //[1, 2]

  SELECT JSON_EXTRACT('[1, 2, 3, 4, 5]', '$[1 to 3]');

  //[2, 3, 4]

  //JSON_SET JSON_INSERT JSON_REPLACE JSON_REMOVE

  SET @j='["a", {"b": [true, false]}, [10, 20]]';

  SELECT JSON_SET(@j, '$[1].b[0]', 1, '$2', 2);

  //| ["a", {"b": [1, false]}, [10, 20, 2]]

  SELECT JSON_INSERT(@j, '$[1].b[0]', 1, '$2', 2);

  //["a", {"b": [true, false]}, [10, 20, 2]]

  JSON_REPLACE(@j, '$[1].b[0]', 1, '$2', 2)

  //["a", {"b": [1, false]}, [10, 20]]

  SELECT JSON_REMOVE(@j, '$[2]', '$[1].b[1]', '$[1].b[1]');

  //["a", {"b": [true]}]JSON Table Functions 一个比较常见的场景是JSON数据本身是一个表的结构;

  JSON_TABLE(expr, path COLUMNS (column_list) [AS] alias)

  SELECT * FROM JSON_TABLE( '[{"a":"3"},{"a":2},{"b":1},{"a":0},{"a":[1,2]}]',

  -> "$[*]"

  -> COLUMNS(

  -> rowid FOR ORDINALITY,

  -> ac VARCHAR(100) PATH "$.a" DEFAULT '111' ON EMPTY DEFAULT '999' ON ERROR,

  -> aj JSON PATH "$.a" DEFAULT '{"x": 333}' ON EMPTY,

  -> bx INT EXISTS PATH "$.b"

  -> )

  -> ) AS tt;Comparison and Ordering of JSON Values

  目前没感觉倒价值;Aggregation of JSON Values

  目前没感觉倒价值; 将返回值转成其他类型就可以使用聚合函数;

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
django 1.8 官方文档翻译:7-2 管理操作
管理操作 简而言之,Django管理后台的基本流程是,“选择一个对象并改变它”。在大多数情况下,这是非常适合的。然而当你一次性要对多个对象做相同的改变,这个流程是非常的单调乏味的。
751 0
mongoDB文档操作
数据库操作无非就是增、删、改、查。这篇主要介绍增、删、改。 1.增 Mongodb插入操作很简单,使用关键字“insert”。实例: 1 > db.test.blog.insert({"haha":"xiaohaha","abc":"123"}) 2 > db.
727 0
阿里云服务器端口号设置
阿里云服务器初级使用者可能面临的问题之一. 使用tomcat或者其他服务器软件设置端口号后,比如 一些不是默认的, mysql的 3306, mssql的1433,有时候打不开网页, 原因是没有在ecs安全组去设置这个端口号. 解决: 点击ecs下网络和安全下的安全组 在弹出的安全组中,如果没有就新建安全组,然后点击配置规则 最后如上图点击添加...或快速创建.   have fun!  将编程看作是一门艺术,而不单单是个技术。
10176 0
JAVASCRIPT中使用DOM操作XML文档
         在 DOM 眼中, HTML 跟 XML 一样是一种树形结构的文档,  是根( root )节点,  、  、  是  的子( children )节点,互相之间是兄弟( sibling )节点;  下面才是子节点  、  、  等等。
519 0
阿里云服务器如何登录?阿里云服务器的三种登录方法
购买阿里云ECS云服务器后如何登录?场景不同,阿里云优惠总结大概有三种登录方式: 登录到ECS云服务器控制台 在ECS云服务器控制台用户可以更改密码、更换系.
12016 0
阿里云ECS云服务器初始化设置教程方法
阿里云ECS云服务器初始化是指将云服务器系统恢复到最初状态的过程,阿里云的服务器初始化是通过更换系统盘来实现的,是免费的,阿里云百科网分享服务器初始化教程: 服务器初始化教程方法 本文的服务器初始化是指将ECS云服务器系统恢复到最初状态,服务器中的数据也会被清空,所以初始化之前一定要先备份好。
11375 0
Lucene7.2.1系列(二)luke使用及索引文档的基本操作
它有以下功能: - 查看文档并分析其内容(用于存储字段) - 在索引中搜索 - 执行索引维护:索引运行状况检查;索引优化(运行前需要备份) - 从hdfs读取索引 - 将索引或其部分导出为XML格式 - 测试定制的Lucene分析工具 - 创建自己的插件
2141 0
+关注
735
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载