MySQL数据库基础：JSON函数各类操作一文详解

目录

 前言

一、JSON语法规则

二、JSON函数

1.JSON_CONTAINS(json_doc,value)函数

2.JSON_SEARCH(json_doc ->> '$[*].key',type,value)函数

 3.JSON_PRETTY(json_doc)函数

 4.JSON_DEPTH(json_doc)函数

 5.JSON_LENGTH(json_doc[,path])函数

 6.JSON_KEYS(json_doc[,path])函数

7. JSON_INSERT(json_doc,path,val[,path,val] ...)函数

 8.JSON_REMOVE(json_doc,path[,path] ...)函数

 9.JSON_REPLACE(json_doc,path,val[,path,val] ...)函数

10.JSON_SET(json_doc,path,val[,path,val] ...)函数

 11.JSON_TYPE(json_val)函数

12. JSON_VALID(value)函数

点关注，防走丢，如有纰漏之处，请留言指教，非常感谢

---

 前言

很多日常业务场景都会用到json文件作为数据存储起来，而mysql5.7以上就提供了存储json的支撑。往常存储json一般都保留在pg库或者是hive库里面，现在mysql有了支持的话基本业务都可以用mysql来实现。现在mysql8.x版本对json字符出处理已经做的非常完善了。现在就让我们来详细了解一下关于json数据数据类型mysql都有哪些函数能够对其进行操作。

该系列文章将按照这个脉络行文，此系列文章将被纳入我的专栏一文速学SQL各类数据库操作，基本覆盖到使用SQL处理日常业务以及常规的查询建库分析以及复杂操作方方面面的问题。从基础的建库建表逐步入门到处理各类数据库复杂操作，以及专业的SQL常用函数讲解都花费了大量时间和心思创作，如果大家有需要从事数据分析或者数据开发的朋友推荐订阅专栏，将在第一时间学习到最实用常用的知识。此篇博客篇幅较长，值得细读实践一番，我会将精华部分挑出细讲实践。博主会长期维护博文，有错误或者疑惑可以在评论区指出，感谢大家的支持。

---

一、JSON语法规则

首先我们还是先复习一遍json数据类型的语法规则，这在我的很多文章都有写到：

JSON是一个标记符的序列。这套标记符包含六个构造字符、字符串、数字和三个字面名。

JSON是一个序列化的对象或数组。

• 数据为  键 / 值 (name/value）对；
• 数据由逗号(,)分隔；
• 大括号保存对象（object）；
• 方括号保存数组（Array）；

值可以是对象、数组、数字、字符串或者三个字面值(false、null、true)中的一个。值中的字面值中的英文必须使用小写。

如：

"code":"100"

对象由花括号括起来的逗号分割的成员构成，成员是字符串键和上文所述的值由逗号分割的键值对组成:

｛“code”:20,"type":"mysql"｝

数组是由方括号括起来的一组值构成:

"datesource":[
    {"code":"20", "type":"mysql"},
   {"code":"20", "type":"mysql"},
    {"code":"20", "type":"mysql"}
]

复习完毕之后我们再来对mysql处理json函数实验。

二、JSON函数

首先我们创建一个表来进行操作：

create TABLE json_test(
	id int not null primary key auto_increment,
	content json
	);

 接下来，向test_json数据表中插入数据。

insert into json_test(content) values('{"name":"fanstuck","age":23,"address":{"province":"zhejiang","city":"hangzhou"}}')

 可以使用“->”和“->>”查询JSON数据中指定的内容。

SELECT content->'$.name' FROM json_test where id =1;

SELECT content->>'$.address.city' FROM json_test where id =1;

1.JSON_CONTAINS(json_doc,value)函数

JSON_CONTAINS(json_doc,value)函数查询JSON类型的字段中是否包含value数据。如果包含则返回1，否则返回0。其中，json_doc为JSON类型的数据，value为要查找的数据。

SELECT JSON_CONTAINS(content, '{"name":"fanstuck"}') FROM json_test ;    

注意：value必须是一个JSON字符串。

2.JSON_SEARCH(json_doc ->> '$[*].key',type,value)函数

JSON_SEARCH(json_doc ->> '$[*].key',type,value)函数在JSON类型的字段指定的key中，查找字符串value。如果找到value值，则返回索引数据。

注意：函数的第二个参数type，取值可以是one或者all。当取值为one时，如果找到value值，则返回value值的第一个索引数据；当取值为all时，如果找到value值，则返回value值的所有索引数据。

SELECT JSON_SEARCH(content ->> '$.address', 'one', 'zhejiang') FROM json_test ;

 

SELECT JSON_SEARCH(content ->> '$.address', 'all', 'nanchang') FROM json_test ;

 3.JSON_PRETTY(json_doc)函数

JSON_PRETTY(json_doc)函数以标准的格式显示JSON数据。

 SELECT JSON_PRETTY(content) FROM json_test ;

 4.JSON_DEPTH(json_doc)函数

JSON_DEPTH(json_doc)函数返回JSON数据的最大深度。

 SELECT JSON_DEPTH(content) FROM json_test;

 

 5.JSON_LENGTH(json_doc[,path])函数

JSON_LENGTH(json_doc[,path])函数返回JSON数据的长度。

SELECT JSON_LENGTH(content) FROM json_test;

 

 6.JSON_KEYS(json_doc[,path])函数

JSON_KEYS(json_doc[,path])函数返回JSON数据中顶层key组成的JSON数组。

 SELECT JSON_KEYS(content) FROM json_test;

 

7. JSON_INSERT(json_doc,path,val[,path,val] ...)函数

JSON_INSERT(json_doc,path,val[,path,val] ...)函数用于向JSON数据中插入数据。

{"age": 23, "name": "fanstuck", "address": {"ip": "192.168.12.12", "city": "hangzhou", "province": "zhejiang"}}

可以看到，JSON_INSERT()函数并没有更新数据表中的数据，只是修改了显示结果。

 8.JSON_REMOVE(json_doc,path[,path] ...)函数

JSON_REMOVE(json_doc,path[,path] ...)函数用于移除JSON数据中指定key的数据。

 SELECT JSON_REMOVE(content, '$.address.city') FROM json_test WHERE id = 2;

 

 9.JSON_REPLACE(json_doc,path,val[,path,val] ...)函数

JSON_REPLACE(json_doc,path,val[,path,val] ...)函数用于更新JSON数据中指定Key的数据。

SELECT JSON_REPLACE(content,'$.age',20) FROM json_test ;

 

 可以看到，JSON_REPLACE()函数并没有更新数据表中的数据，只是修改了显示结果。

10.JSON_SET(json_doc,path,val[,path,val] ...)函数

JSON_SET(json_doc,path,val[,path,val] ...)函数用于向JSON数据中插入数据。

 SELECT JSON_SET(content, '$.address.street', 'xxx街道') FROM json_test WHERE id = 1;

 11.JSON_TYPE(json_val)函数

JSON_TYPE(json_val)函数用于返回JSON数据的JSON类型，MySQL中支持的JSON类型除了可以是MySQL中的数据类型外，还可以是OBJECT和ARRAY类型，其中OBJECT表示JSON对象，ARRAY表示JSON数组。

 SELECT JSON_TYPE(content) FROM json_test ;

12. JSON_VALID(value)函数

JSON_VALID(value)函数用于判断value的值是否是有效的JSON数据，如果是，则返回1，否则返回0，如果value的值为NULL，则返回NULL。

 SELECT JSON_VALID('{"name":"binghe"}'), JSON_VALID('name'), JSON_VALID(NULL);

点关注，防走丢，如有纰漏之处，请留言指教，非常感谢

以上就是本期全部内容。我是fanstuck ，有问题大家随时留言讨论 ，我们下期见