Mysql json 数据查询

邱秋 • 2022年08月16日 • 阅读:432 • mysql

Json 类型简介

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

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

假定表结构如下:

id data
1 {'name':'李磊','age':28}
2 {'name':'张磊','age':38}

一维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查询

id data
1 [{'name': '李磊', age:28 } , {'name': '韩梅梅', age:25 }]
2 [{'name': '张磊', age:38 } , {'name': '珀丽', age:35 }]

查询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 表数据结构如下

id tags
1 ['linux', 'centos', 'mac']
2 ['linux' , 'windows']
2 ['mac' , 'windows']

查询 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;

输出:

李磊

列名中包含点符号

id data
1 { 'name': '李磊', 'no.' : '1234567' }

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

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指定列的关系表返回。

我,秦始皇,打钱!