MYSQL中的json数据操作代码

MYSQL中的json数据操作

1.2 基础查询操作

用法提示：

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

1.2.1 一般json查询

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

?

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

1.2.2 多个条件查询

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

?

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

1.2.3 json中多个字段关系查询

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

?

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

1.2.4 关联表查询

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

?

1 2	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字段名)

?

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

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

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

?

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

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

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

?

1	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] …])
事例：

?

1 2 3

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表中：

?

1 2	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的数据

?

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

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

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

?

1	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

?

1	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语句如下：

?

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

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

?

1	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] …)
事例：

?

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

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

1.3.11 JSON_REPLACE()

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

?

1	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的字段。

?

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

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

原文链接：https://www.cnblogs.com/guanyuan12/p/16738392.html