MySQL 5.7 JSON 数据类型使用总结

文章目录

一、JSON数据类型简介
二、简单使用示例
数据准备
数据查询
数据修改
其他函数
总结
三、JSON 函数概览
一、JSON数据类型简介
从版本5.7.8开始，mysql开始支持json数据类型，json数据类型存储时会做格式检验，不满足json格式会报错，json数据类型默认值不允许为空。

二、简单使用示例
数据准备

create table json_tab
(
  id        int unsigned primary key auto_increment comment '主键',
  json_info json comment 'json数据',
  json_id   int generated always as (json_info -> '$.id') comment 'json数据的虚拟字段',
  index json_info_id_idx (json_id)
)
  comment 'json示例表';

insert into json_tab(json_info)
values ('{"id": 1, "name": "张三", "age": 18, "sister": [{"name": "张大姐", "age": 30}, {"name": "张二姐", "age": 20}]}');
insert into json_tab(json_info)
values (JSON_OBJECT('id', 2, 'name', '李四', 'age', 18, 'sister', JSON_ARRAY(JSON_OBJECT('name', '李大姐', 'age', 28), JSON_OBJECT('name', '李二姐', 'age', 25))));
insert into json_tab(json_info)
values ('{"id": 3, "name": "小明", "age": 18, "sister": [{"name": "小明大姐", "age": 25, "friend": [{"name": "大姐朋友一", "age": 25}, {"name": "大姐朋友二", "age": 25}]}, {"name": "小明二姐", "age": 20, "friend": [{"name": "二姐朋友一", "age": 22}, {"name": "二姐朋友二", "age": 21}]}]}');

json_id 是虚拟列，插入数据时不需要往该字段插入值，json数据类型不能直接建立索引，需要通过建立虚拟列再将索引建在虚拟列上这样的方式来建立索引；

json字段插入数据时有两种方式，一种是直接插入满足json格式的字符串，不符合json格式的字符串插入时会报错；另一种是通过JSON_OBJECT、JSON_ARRAY这两个json函数先构建好json数据再插入。

数据查询

# 先看看数据，注意虚拟列json_id，未插入值确显示有值
select * from json_tab;
select * from json_tab order by json_id desc;
select * from json_tab where json_info -> '$.name' = '李四';
# JSON_TYPE 函数判断JSON数据类型
select JSON_TYPE(json_info)               as info_type,
       JSON_TYPE(json_info -> '$.age')    as age_type,
       JSON_TYPE(json_info -> '$.name')   as name_type,
       JSON_TYPE(json_info -> '$.sister') as sister_type
from json_tab;
# 查询姓名以及他们的年龄
select json_info -> '$.name' as name, json_info -> '$.age' as age
from json_tab;
select json_info -> '$**.name' as name, json_info -> '$**.age' as age
from json_tab;
# -> 等价于 JSON_EXTRACT(column, path)
select JSON_EXTRACT(json_info, '$.name') as name, JSON_EXTRACT(json_info, '$.age') as age
from json_tab;
# 去掉双引号
select json_info ->> '$.name' as name, json_info -> '$.age' as age
from json_tab;
# ->> 等价于 JSON_UNQUOTE(JSON_EXTRACT(column, path))
select JSON_UNQUOTE(JSON_EXTRACT(json_info, '$.name')) as name, JSON_EXTRACT(json_info, '$.age') as age
from json_tab;
# 查询姓名和他们的姐姐
select json_info ->> '$.name' as name, json_info ->> '$.sister[*].name' as sisters
from json_tab;
# 查询姓名和他们的姐姐以及朋友
select json_info ->> '$.name' as name, json_info ->> '$.sister[*].name' as sisters, json_info -> '$**.friend[*].name' as friends
from json_tab;
# 查询key
# JSON_KEYS(json_doc[, path])
select JSON_KEYS(json_info) from json_tab;
select JSON_KEYS(json_info, '$.sister[1]') from json_tab;
# 查询名字是否存在
# JSON_CONTAINS(target, candidate[, path])
select JSON_CONTAINS(json_info, '"张三"', '$.name') from json_tab;
# 查询是否包含路径
# JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] ...)
# one 至少存在一个路径
select JSON_CONTAINS_PATH(json_info,'one', '$.name', '$.xxx') from json_tab;
# all 所有路径都存在
select JSON_CONTAINS_PATH(json_info,'all', '$.name', '$**.friend') from json_tab;
# 查询字符串的路径
# JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...])
# one 只匹配第一个
select JSON_SEARCH(json_info,'one', '张三') from json_tab;
select JSON_SEARCH(json_info,'one', '%朋友%', null, '$**.name') from json_tab;
# all 找到所有的路径
select JSON_SEARCH(json_info,'all', '%朋友_', '朋', '$**.name') from json_tab;

数据修改

# 插入json数组
insert into json_tab(json_info) values ('[1, {"a": "b"}, [2, "qwe"]]');
# json数组指定位置上追加数据，path可以不是数组元素，结果都是数组
# JSON_ARRAY_APPEND(json_doc, path, val[, path, val] ...)
select * from json_tab;
select JSON_ARRAY_APPEND(json_info, '$', 1) from json_tab where id=4;
select JSON_ARRAY_APPEND(json_info, '$[0]', 1) from json_tab where id=4;
select JSON_ARRAY_APPEND(json_info, '$[2]', 1) from json_tab where id=4;
select JSON_ARRAY_APPEND(json_info, '$[1].a', 1) from json_tab where id=4;
# json数组指定位置前插入数据，path必须是数组元素
# JSON_ARRAY_INSERT(json_doc, path, val[, path, val] ...)
select JSON_ARRAY_INSERT(json_info, '$[1]', 1) from json_tab where id=4;
# json指定位置插入值，已存在的path忽略，类似map.putIfAbsent
# JSON_INSERT(json_doc, path, val[, path, val] ...)
select JSON_INSERT(json_info, '$[1].b', 'bbb') from json_tab where id=4;
select JSON_INSERT(json_info, '$[2]', 'aaa') from json_tab where id=4;
select JSON_INSERT(json_info, '$[3]', 'aaa') from json_tab where id=4;
# json指定位置设置值，已存在的替换，不存在的新增，类似map.put
# JSON_SET(json_doc, path, val[, path, val] ...)
select JSON_SET(json_info, '$[1].b', 'bbb') from json_tab where id=4;
select JSON_SET(json_info, '$[2]', 'aaa') from json_tab where id=4;
select JSON_SET(json_info, '$[3]', 'aaa') from json_tab where id=4;
# json指定位置替换值，已存在的替换，不存在的忽略，类似map.replace
# JSON_REPLACE(json_doc, path, val[, path, val] ...)
select JSON_REPLACE(json_info, '$[1].b', 'bbb') from json_tab where id=4;
select JSON_REPLACE(json_info, '$[2]', 'aaa') from json_tab where id=4;
select JSON_REPLACE(json_info, '$[3]', 'aaa') from json_tab where id=4;
# 删除指定位置元素
# JSON_REMOVE(json_doc, path[, path] ...)
select JSON_REMOVE(json_info, '$[1].a') from json_tab where id=4;
select JSON_REMOVE(json_info, '$[2]') from json_tab where id=4;

其他函数

# 查询json数据的深度
# JSON_DEPTH(json_doc)
select JSON_DEPTH(json_info),json_info from json_tab;
# 查询json数据的长度
# JSON_LENGTH(json_doc[, path])
select JSON_LENGTH(json_info),json_info from json_tab;
# 验证字符串是否有效的json格式
# JSON_VALID(val)
SELECT JSON_VALID('{"a": 1}');
SELECT JSON_VALID('hello'), JSON_VALID('"hello"');
# JSON_QUOTE(string)
SELECT JSON_QUOTE('null'), JSON_QUOTE('"null"');
SELECT JSON_QUOTE('[1, 2, 3]');

 

总结

路径不允许使用通配符的函数：

JSON_KEYS(json_doc[, path])
JSON_CONTAINS(target, candidate[, path])
JSON_ARRAY_APPEND(json_doc, path, val[, path, val] …)
JSON_INSERT(json_doc, path, val[, path, val] …)
JSON_REMOVE(json_doc, path[, path] …)
JSON_REPLACE(json_doc, path, val[, path, val] …)
JSON_SET(json_doc, path, val[, path, val] …)
JSON_LENGTH(json_doc[, path])
5.7.22新增函数：

JSON_MERGE_PATCH
JSON_MERGE_PRESERVE
JSON_STORAGE_SIZE
JSON_PRETTY
不在这里介绍，可以点击下面函数概览的链接查阅官方文档。

三、JSON 函数概览

Table 12.22 JSON Functions

Name	Description	Introduced	Deprecated
->	Return value from JSON column after evaluating path; equivalent to JSON_EXTRACT().
->>	Return value from JSON column after evaluating path and unquoting the result; equivalent to JSON_UNQUOTE(JSON_EXTRACT()).
JSON_ARRAY()	Create JSON array
JSON_ARRAY_APPEND()	Append data to JSON document
JSON_ARRAY_INSERT()	Insert into JSON array
JSON_CONTAINS()	Whether JSON document contains specific object at path
JSON_CONTAINS_PATH()	Whether JSON document contains any data at path
JSON_DEPTH()	Maximum depth of JSON document
JSON_EXTRACT()	Return data from JSON document
JSON_INSERT()	Insert data into JSON document
JSON_KEYS()	Array of keys from JSON document
JSON_LENGTH()	Number of elements in JSON document
JSON_MERGE()	Merge JSON documents, preserving duplicate keys. Deprecated synonym for JSON_MERGE_PRESERVE()		Yes
JSON_MERGE_PATCH()	Merge JSON documents, replacing values of duplicate keys
JSON_MERGE_PRESERVE()	Merge JSON documents, preserving duplicate keys
JSON_OBJECT()	Create JSON object
JSON_OVERLAPS()	Compares two JSON documents, returns TRUE (1) if these have any key-value pairs or array elements in common, otherwise FALSE (0)	8.0.17
JSON_PRETTY()	Print a JSON document in human-readable format
JSON_QUOTE()	Quote JSON document
JSON_REMOVE()	Remove data from JSON document
JSON_REPLACE()	Replace values in JSON document
JSON_SCHEMA_VALID()	Validate JSON document against JSON schema; returns TRUE/1 if document validates against schema, or FALSE/0 if it does not	8.0.17
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	8.0.17
JSON_SEARCH()	Path to value within JSON document
JSON_SET()	Insert data into JSON document
JSON_STORAGE_FREE()	Freed space within binary representation of JSON column value following partial update
JSON_STORAGE_SIZE()	Space used for storage of binary representation of a JSON document
JSON_TABLE()	Return data from a JSON expression as a relational table
JSON_TYPE()	Type of JSON value
JSON_UNQUOTE()	Unquote JSON value
JSON_VALID()	Whether JSON value is valid
JSON_VALUE()	Extract value from JSON document at location pointed to by path provided; return this value as VARCHAR(512) or specified type	8.0.21
MEMBER OF()	Returns true (1) if first operand matches any element of JSON array passed as second operand, otherwise returns false (0)	8.0.17

 

mysql官方文档

引用：https://blog.csdn.net/u011207553/article/details/88912219