MYSQL中的json数据操作代码

目录
  • MYSQL中的json数据操作
  • 1.2 基础查询操作
    • 1.2.1 一般json查询
    • 1.2.2 多个条件查询
    • 1.2.3 json中多个字段关系查询
    • 1.2.4 关联表查询
  • 1.3 JSON函数操作
    • 1.3.1 官方json函数
    • 1.3.2 ->、->>区别
      • 1.3.2.2 在where条件中使用
    • 1.3.3 json_extract():从json中返回想要的字段
    • 1.3.4 JSON_CONTAINS():JSON格式数据是否在字段中包含特定对象
    • 1.3.5 SON_OBJECT():将一个键值对列表转换成json对象
    • 1.3.6 JSON_ARRAY():创建JSON数组
    • 1.3.7 JSON_TYPE():查询某个json字段属性类型
    • 1.3.8 JSON_KEYS():JSON文档中的键数组
    • 1.3.10 JSON_INSERT():插入值(往json中插入新值,但不替换已经存在的旧值)
    • 1.3.11 JSON_REPLACE()
    • 1.3.12 JSON_REMOVE():从JSON文档中删除数据

MYSQL中的json数据操作

1.2 基础查询操作

用法提示:

  • 如果json字符串不是数组,则直接使用$.字段名
  • 如果json字符串是数组[Array],则直接使用$[对应元素的索引id]

1.2.1 一般json查询

使用json字段名->’$.json属性’进行查询条件,注意:如果 ‘->’ 不能用也可用 ‘->>’ 查询
举个例子:如果想查询deptLeader=张五的数据,那么sql语句如下:

SELECT * from dept WHERE json_value->'$.deptLeaderId'='5';

1.2.2 多个条件查询

比如想查dept为“部门3”和deptLeaderId=5的数据,sql如下:

SELECT * from dept WHERE json_value->'$.deptLeaderId'='5' and json_value->'$.deptId'='5';

1.2.3 json中多个字段关系查询

比如想查询json格式中deptLeader=张五和deptId=5的数据

SELECT * from dept WHERE json_value->'$.deptLeaderId'='5' and json_value->'$.deptId'='5';

1.2.4 关联表查询

这里我们要连表查询在dept 表中部门leader在dept_leader 中的详情

SELECT * from dept,dept_leader
WHERE dept.json_value->'$.deptLeaderId'=dept_leader.json_value->'$.id' ;

1.3 JSON函数操作

写到这里大家都发现了,我们查询的json都是整条json数据,这样看起来不是很方便,那么如果我们只想看json中的某个字段怎么办?

1.3.1 官方json函数

Name Description 解释
-> Return value from JSON column after evaluating path; equivalent to JSON_EXTRACT() 计算路径后返回JSON列的值;相当于JSON_EXTRACT ()
->> Return value from JSON column after evaluating path and unquoting the result; equivalent to JSON_UNQUOTE(JSON_EXTRACT()). 从JSON列返回值后,就算路径和取消引号的结果;相当于JSON_UNQUOTE (JSON_EXTRACT ())
JSON_ARRAY() Create JSON array 创建JSON数组
JSON_ARRAY_APPEND() Append data to JSON document 向JSON文档追加数据
JSON_ARRAY_INSERT() Insert into JSON array 插入JSON数组
JSON_CONTAINS() Whether JSON document contains specific object at path JSON文档是否包含路径上的特定对象
JSON_CONTAINS_PATH() Whether JSON document contains any data at path JSON文档是否在路径上包含任何数据
JSON_DEPTH() Maximum depth of JSON document JSON文档的最大深度
JSON_EXTRACT() Return data from JSON document 从JSON文档返回数据
JSON_INSERT() Insert data into JSON document 将数据插入JSON文档
JSON_KEYS() Array of keys from JSON document 来自JSON文档的键数组
JSON_LENGTH() Number of elements in JSON document JSON文档中的元素数量
JSON_MERGE_PATCH() Merge JSON documents, replacing values of duplicate keys 合并JSON文档,替换重复键的值
JSON_MERGE_PRESERVE() Merge JSON documents, preserving duplicate keys 合并JSON文档,保留重复的密钥
JSON_OBJECT() Create JSON object 创建JSON对象
JSON_OVERLAPS() Compares two JSON documents, returns TRUE (1) if these have any key-value pairs or array elements in common, otherwise FALSE (0) 比较两个JSON文档,如果它们有共同的键值对或数组元素,则返回TRUE(1),否则返回FALSE (0)
JSON_PRETTY() Print a JSON document in human-readable format 以人类可读的格式打印JSON文档
JSON_QUOTE() Quote JSON document 引用JSON文档
JSON_REMOVE() Remove data from JSON document 从JSON文档中删除数据
JSON_REPLACE() Replace values in JSON document 替换JSON文档中的值
JSON_SCHEMA_VALID() Validate JSON document against JSON schema; returns TRUE/1 if document validates against schema, or FALSE/0 if it does not 针对JSON模式验证JSON文档;如果文档针对模式进行验证,则返回TRUE/1,否则返回FALSE/0
JSON_SCHEMA_VALIDATION_REPORT() Validate JSON document against JSON schema; returns report in JSON format on outcome on validation including success or failure and reasons for failure 针对JSON模式验证JSON文档;以JSON格式返回关于验证结果的报告,包括成功或失败以及失败原因
JSON_SEARCH() Path to value within JSON document JSON文档中值的路径
JSON_SET() Insert data into JSON document 将数据插入JSON文档
JSON_STORAGE_FREE() Freed space within binary representation of JSON column value following partial update 在部分更新后释放JSON列值的二进制表示形式中的空间
JSON_STORAGE_SIZE() pace used for storage of binary representation of a JSON document 用于存储JSON文档的二进制表示的空间
JSON_TABLE() Return data from a JSON expression as a relational table 以关系表的形式从JSON表达式返回数据
JSON_TYPE() Type of JSON value JSON值类型
JSON_UNQUOTE() Unquote JSON value 不引用JSON值
JSON_VALID() Whether JSON value is valid JSON值是否有效
JSON_VALUE() Extract value from JSON document at location pointed to by path provided; return this value as VARCHAR(512) or specified type 根据所提供的路径从JSON文档中所指向的位置提取值;返回该值为VARCHAR(512)或指定的类型
MEMBER OF() Returns true (1) if first operand matches any element of JSON array passed as second operand, otherwise returns false (0) 如果第一个操作数匹配作为第二个操作数的JSON数组中的任何元素,则返回true(1),否则返回false (0)

1.3.2 ->、->>区别

->field中使用的时候结果带引号,->>的结果不带引号

1.3.2.2 在where条件中使用

特别注意:->当做where查询是要注意类型的,->>是不用注意类型的

1.3.3 json_extract():从json中返回想要的字段

用法:json_extract(字段名,$.json字段名)

select id,json_extract(json_value,'$.deptName') as deptName from dept;

1.3.4 JSON_CONTAINS():JSON格式数据是否在字段中包含特定对象

用法: JSON_CONTAINS(target, candidate[, path])
事例:如果我们想查询包含deptName=部门5的对象

select * from dept WHERE JSON_CONTAINS(json_value, JSON_OBJECT("deptName","部门5"))

1.3.5 SON_OBJECT():将一个键值对列表转换成json对象

比如我们想查询某个对象里面的值等于多少
比如我们添加这么一组数据到dept表中:

insert into dept VALUES(6,'部门9','{"deptName": {"dept":"de","depp":"dd"}, "deptId": "5", "deptLeaderId": "5"}');

我们可以看到deptName中还有一个对象,里面还有dept和depp两个属性字段,那么我们应该怎么查询depp=dd的员工呢。

用法:JSON_OBJECT([key, val[, key, val] …])
事例:

SELECT * from (
    SELECT *,json_value->'$.deptName' as deptName FROM dept
) t WHERE JSON_CONTAINS(deptName,JSON_OBJECT("depp","dd"));

1.3.6 JSON_ARRAY():创建JSON数组

比如我们添加这么一组数据到dept表中:

insert into dept VALUES(7,'部门9','{"deptName": ["1","2","3"], "deptId": "5", "deptLeaderId": "5"}');
insert into dept VALUES(7,'部门9','{"deptName": ["5","6","7"], "deptId": "5", "deptLeaderId": "5"}');

用法:JSON_ARRAY([val[, val] …])

事例:我们要查询deptName包含1的数据

SELECT * from dept WHERE JSON_CONTAINS(json_value->'$.deptName',JSON_ARRAY("1"))

1.3.7 JSON_TYPE():查询某个json字段属性类型

用法:JSON_TYPE(json_val)
事例:比如我们想查询deptName的字段属性是什么

SELECT json_value->'$.deptName' ,JSON_TYPE(json_value->'$.deptName') as type from dept

1.3.8 JSON_KEYS():JSON文档中的键数组

用法:JSON_KEYS(json_value)
事例:比如我们想查询json格式数据中的所有key

SELECT JSON_KEYS(json_value) FROM dept 

接下来的3种函数都是新增数据类型的:
JSON_SET(json_doc, path, val[, path, val] …)
JSON_INSERT(json_doc, path, val[, path, val] …)
JSON_REPLACE(json_doc, path, val[, path, val] …)1.3.9 JSON_SET():将数据插入JSON格式中,有key则替换,无key则新增

这也是我们开发过程中经常会用到的一个函数
用法:JSON_SET(json_doc, path, val[, path, val] …)
事例:比如我们想针对id=2的数据新增一组:newData:新增的数据,修改deptName为新增的部门1
sql语句如下:

update dept set json_value=JSON_SET('{"deptName": "部门2", "deptId": "2", "deptLeaderId": "4"}','$.deptName','新增的部门1','$.newData','新增的数据') WHERE id=2;

注意json_doc如果不带这个单元格之前的值,之前的值是会新值被覆盖的,比如我们如果更新的语句换成:

update dept set json_value=JSON_SET('{"a":"1","b":"2"}','$.deptName','新增的部门1','$.newData','新增的数据') WHERE id=2我们可以看到这里json_doc是{“a”:“1”,“b”:“2”},这样的话会把之前的单元格值覆盖后再新增/覆盖这个单元格字段

1.3.10 JSON_INSERT():插入值(往json中插入新值,但不替换已经存在的旧值)

用法:JSON_INSERT(json_doc, path, val[, path, val] …)
事例:

UPDATE dept set json_value=JSON_INSERT('{"a": "1", "b": "2"}', '$.deptName', '新增的部门2','$.newData2','新增的数据2')
WHERE id=2

我们可以看到由于json_doc变化将之前的值覆盖了,新增了deptNamenewData2.
如果我们再执行以下刚才的那个sql,只是换了value,我们会看到里面的key值不会发生变化。
因为这个函数只负责往json中插入新值,但不替换已经存在的旧值。

1.3.11 JSON_REPLACE()

用法:JSON_REPLACE(json_doc, path, val[, path, val] …)
用例:
如果我们要更新id=2数据中newData2的值为:更新的数据2
sql语句如下:

UPDATE dept set json_value=JSON_REPLACE('{"a": "1", "b": "2", "deptName": "新增的部门2", "newData2": "新增的数据2"}', '$.newData2', '更新的数据2') WHERE id =2;

1.3.12 JSON_REMOVE():从JSON文档中删除数据

用法:JSON_REMOVE(json_doc, path[, path] …)
举例:删除key为a的字段。

UPDATE dept set json_value=JSON_REMOVE('{"a": "1", "b": "2", "deptName": "新增的部门2", "newData2": "更新的数据2"}','$.a') WHERE id =2;

到此这篇关于MYSQL中的json数据操作的文章就介绍到这了,更多相关mysql json数据内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

(0)

相关推荐

  • mysql json格式数据查询操作

    预设表名是 base_data , json列名是 json_value json_value的值是 {"ids":["a","b"],"datas":[{"name":"张三"}]} 那么查询语句应该是 select * from base_data where JSON_CONTAINS(json_value -> "$.ids", JSON_ARRAY('

  • MySQL操作之JSON数据类型操作详解

    上一篇文章我们介绍了mysql数据存储过程参数实例详解,今天我们看看MySQL操作之JSON数据类型的相关内容. 概述 mysql自5.7.8版本开始,就支持了json结构的数据存储和查询,这表明了mysql也在不断的学习和增加nosql数据库的有点.但mysql毕竟是关系型数据库,在处理json这种非结构化的数据时,还是比较别扭的. 创建一个JSON字段的表 首先先创建一个表,这个表包含一个json格式的字段: CREATE TABLE table_name ( id INT NOT NULL

  • Mysql如何对json数据进行查询及修改

    目录 对json数据进行查询及修改 几个相关函数 示例 查询 来看看修改 删除 插入 Mysql处理json数据 对json数据进行查询及修改 使用 字段->'$.json属性' 进行查询条件 使用 json_extract 函数查询,json_extract(字段, "$.json属性") 根据json数组查询,用 JSON_CONTAINS(字段, JSON_OBJECT('json属性', "内容")) : [{}]查询这种形式的json数组 MySQL

  • Mysql怎么存储json格式数据详解

    目录 前言 JSON 数据类型推荐使用在不经常更新的静态数据存储 查询 json数据 增加索引 使用场景 总结 前言 Mysql5.7版本以后新增的功能,Mysql提供了一个原生的Json类型,Json值将不再以字符串的形式存储,而是采用一种允许快速读取文本元素(document elements)的内部二进制(internal binary)格式.在Json列插入或者更新的时候将会自动验证Json文本,未通过验证的文本将产生一个错误信息 JSON 数据类型推荐使用在不经常更新的静态数据存储 创

  • MYSQL中的json数据操作代码

    目录 MYSQL中的json数据操作 1.2 基础查询操作 1.2.1 一般json查询 1.2.2 多个条件查询 1.2.3 json中多个字段关系查询 1.2.4 关联表查询 1.3 JSON函数操作 1.3.1 官方json函数 1.3.2 ->.->>区别 1.3.2.2 在where条件中使用 1.3.3 json_extract():从json中返回想要的字段 1.3.4 JSON_CONTAINS():JSON格式数据是否在字段中包含特定对象 1.3.5 SON_OBJEC

  • 详解Mysql中的JSON系列操作函数

    前言 JSON是一种轻量级的数据交换格式,采用了独立于语言的文本格式,类似XML,但是比XML简单,易读并且易编写.对机器来说易于解析和生成,并且会减少网络带宽的传输. JSON的格式非常简单:名称/键值.之前MySQL版本里面要实现这样的存储,要么用VARCHAR要么用TEXT大文本. MySQL5.7发布后,专门设计了JSON数据类型以及关于这种类型的检索以及其他函数解析. 下面一起来实际操作一下. 创建带有 JSON 字段的表 比如一个'文章'表,字段包括 id.标题 title.标签 t

  • Android 中对JSON数据解析实例代码

    Android 中对JSON数据解析 在Android的网络编程中,JSON是比XML使用更为广泛的数据传输机制.在许多的http网络请求或接口调用中,返回的很多都是JSON.所以学会解析JSON是学会Android的基本技能. 简单介绍下JSON,JSON是轻量级的文本数据交换格式,独立于语言和平台.比XML更小,更快,更易解析.JSON是用键值对来保存数据的.JSON保存的值可以是数字.字符串.布尔值.数组和对象. 废话少说,直接上代码.下面对以下JOSN进行解析: { "language&

  • hibernate存取json数据的代码分析

    一.场景 public class OrderModel { private List<String> favorableDescList; } 订单中会存储一些优惠信息,方便页面展示时使用,如: 1.满100减50 2.参与[老会员真情回馈--精品课程体验活动],仅需支付200.00学币 3.[Oracle + PL/SQL 实战]套装课程的[抢购]活动,优惠120.00学币 --等等 如图所示,我们在页面给用户展示他们参与的优惠信息: 二.分析 如上优惠信息有如下特点: 1.只用于展示,不

  • PHP5.5基于mysqli连接MySQL数据库和读取数据操作实例详解

    本文实例讲述了PHP5.5基于mysqli连接MySQL数据库和读取数据操作.分享给大家供大家参考,具体如下: 在学习1. 开启PHP的API支持 (1)首先修改您的php.ini的配置文件. 查找下面的语句: ;extension=php_mysqli.dll 将其修改为: extension=php_mysqli.dll (2)重新启动Apache/IIS,即可. (3)说明:PHP需要单独的文件来支持这个扩展库,一般在PHP目录下的ext目录里能找到php_mysqli.dll文件(PHP

  • Django+RestFramework API接口及接口文档并返回json数据操作

    系统:ubuntu18.04 x64 GitHub:https://github.com/xingjidemimi/DjangoAPI.git 安装 pip install django==2.1.5 pip install djangorestframework # rest api pip install coreapi pygments markdown # 自动化接口文档 API示例 创建django项目 django-admin startproject DjangoAPI 创建应用

  • MySQL 中如何归档数据的实现方法

    目录 什么是pt-archiver 安装 简单入门 实现原理 批量归档 不同归档参数之间的速度对比 其它常见用法 (1)删除数据 (2)将数据归档到文件中 如何避免主从延迟 常用参数 总结 归档,在 MySQL 中,是一个相对高频的操作. 它通常涉及以下两个动作: 迁移.将数据从业务实例迁移到归档实例. 删除.从业务实例中删除已迁移的数据. 在处理类似需求时,都是开发童鞋提单给 DBA,由 DBA 来处理. 于是,很多开发童鞋就好奇,DBA 都是怎么执行归档操作的?归档条件没有索引会锁表吗?安全

  • MySQL中增删改查操作与常见陷阱详解

    目录 本文导读 一.MySQL的增删改查 1.insert语句 2.delete语句 3.update语句原理 4.select 二.15种MySQL数据操作语句 1.REPLACE语句 2.CALL语句 3.TABLE语句 4.WITH语句 三.MySQL查询陷阱 总结 本文导读 本文作为MySQL系列第二篇文章,详细讲解了MySQL的增删改查的语句.语义和一些我们经常在开发工作中暴露的问题,MySQL的增删改查又叫数据操作语句,本文有讲些了一些常用的数据操作语句,select语句后续将作为一

  • 在JavaScript中使用JSON数据

    JSON 是 JavaScript 原生格式,这意味着 在 JavaScript 中处理 JSON 数据不需要任何特殊的 API 或工具包. JSON语法 JSON建构于两种结构: 对象--名称/值对的集合.不同的语言中,它被理解为对象,纪录,结构,字典,哈希表,有键列表(keyed list),或者关联数组.一个对象以"{"(左括号)开始,"}"(右括号)结束.每个"名称"后跟一个":"(冒号):"'名称/值' 对

  • python pandas中DataFrame类型数据操作函数的方法

    python数据分析工具pandas中DataFrame和Series作为主要的数据结构. 本文主要是介绍如何对DataFrame数据进行操作并结合一个实例测试操作函数. 1)查看DataFrame数据及属性 df_obj = DataFrame() #创建DataFrame对象 df_obj.dtypes #查看各行的数据格式 df_obj['列名'].astype(int)#转换某列的数据类型 df_obj.head() #查看前几行的数据,默认前5行 df_obj.tail() #查看后几

随机推荐