Mysql json 数据查询
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 cpjson数据格式化 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指定列的关系表返回。
本文参与 腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。
原始发表：2022-08-16 ，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
目录
相关产品与服务
数据库
云数据库为企业提供了完善的关系型数据库、非关系型数据库、分析型数据库和数据库生态工具。您可以通过产品选择和组合搭建，轻松实现高可靠、高可用性、高性能等数据库需求。云数据库服务也可大幅减少您的运维工作量，更专注于业务发展，让企业一站式享受数据上云及分布式架构的技术红利！