Json 类型简介

MySQL 5.7 之后提供了Json类型，是MySQL 结合结构化存储和非结构化存储设计出来的一个类型。

在某些场景下，Json 类型简直是福音。

假定表结构如下：

一维json查询

利用函数

select * from user where json_extract(data,'$.age')= 28;
--or
select * from user where json_contains(data,json_object('age',28));

高级查询

select * from user where data->'$.age'=28;
-- or
select data->'$.age' from user where data->'$.age'=28;

列过滤 和having

select json_extract(data,'$.age') from user where json_extract(data,'$.age')= 28;
-- or
select json_extract(data,'$.age') as age from user having age =28;

二维json查询

查询age为28 的数据

select * from user where json_contains(data,json_object('age',28));
-- or
select json_extract(data,'$.age') from user where json_extract(data,'$.age')= 28;

数组查询

假设 device 表数据结构如下

查询 windows 类型

SELECT * from device WHERE JSON_CONTAINS(tags, '"windows"');
-- or
SELECT * from device WHERE JSON_CONTAINS(tags, '"windows"',$);

但是在实际开发中，单引号和双引号之间串联匹配会有问题，所有还可以这么干：

SELECT * from device WHERE JSON_CONTAINS(tags, json_array('windows'));

给Json中的字段添加索引

-- 增加虚拟列- age，值通过data 计算而来
alter table user add COLUMN age int as (data->>"$.age");
-- 给 age 这一列增加唯一索引
alter table user add unique index idex_age(age);

--查询
select * from user where age = 28

引号JSON_UNQUOTE

选择一个JSON字段

从json中选择特定的字段， 例如：

SELECT JSON_EXTRACT(data,'$.name') AS  name FROM user;

输出：

"李磊"

从选择结果中删除双引号JSON_UNQUOTE,->和->>

-- 带引号
SELECT loginInfo->"$.name" from UserLogin;
-- 不带引号
SELECT loginInfo->>"$.name" from UserLogin;
SELECT JSON_UNQUOTE(JSON_EXTRACT(data,'$.name')) AS  name FROM user;

输出：

李磊

列名中包含点符号

不能直接在选择字段中使用点符号，因为它将被视为分母。可以用双引号将其引起来：

SELECT JSON_UNQUOTE(JSON_EXTRACT(data,'$."no."')) AS  mobile FROM users;
-- output 1234567

判断Json中是否有对应字段 JSON_CONTAINS_PATH

所有记录中有多少记录包含name字段

SELECT count(*), JSON_CONTAINS_PATH(data, 'one', '$.name') cp FROM user GROUP BY cp

json数据格式化 JSON_PRETTY

SELECT JSON_PRETTY(data) from user

返回格式化的json数据

{
  "name":"李磊",
  "age":28
}

计算json 字节数 JSON_STORAGE_SIZE

返回data 字段中存储的二进制表示的字节数。

SELECT max(JSON_STORAGE_SIZE(data)) FROM user;
SELECT avg(JSON_STORAGE_SIZE(data)) FROM user;
SELECT min(JSON_STORAGE_SIZE(data)) FROM user;

其他函数

• JSON_OBJECT 计算键值对列表并返回包含这些键值对的JSON对象，使用JSON_OBJECT。
• JSON_OBJECTAGG 接受两个列名或表达式，并返回一个包含JSON_OBJECTAGG键值对的JSON对象。
• JSON_ARRAY 计算一个值列表，并使用JSON_ARRAY返回包含这些值的JSON数组。
• JSON_ARRAYAGG 将结果集聚合为单个JSON数组，其元素由带有JSON_ARRAYAGG的行组成。
• JSON_TABLE 从JSON文档中提取数据，并将其作为具有JSON_TABLE指定列的关系表返回。