前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >Mysql json 数据查询

Mysql json 数据查询

作者头像
chuchur
发布2022-10-25 15:02:30
4.5K0
发布2022-10-25 15:02:30
举报
文章被收录于专栏:禅境花园

Json 类型简介

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

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

假定表结构如下:

id

data

1

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

2

{'name':'张磊','age':38}

一维json查询

利用函数

代码语言:javascript
复制
select * from user where json_extract(data,'$.age')= 28;
--or
select * from user where json_contains(data,json_object('age',28));

高级查询

代码语言:javascript
复制
select * from user where data->'$.age'=28;
-- or
select data->'$.age' from user where data->'$.age'=28;

列过滤 和having

代码语言:javascript
复制
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 的数据

代码语言:javascript
复制
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 类型

代码语言:javascript
复制
SELECT * from device WHERE JSON_CONTAINS(tags, '"windows"');
-- or
SELECT * from device WHERE JSON_CONTAINS(tags, '"windows"',$);

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

代码语言:javascript
复制
SELECT * from device WHERE JSON_CONTAINS(tags, json_array('windows'));

给Json中的字段添加索引

代码语言:javascript
复制
-- 增加虚拟列- 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中选择特定的字段, 例如:

代码语言:javascript
复制
SELECT JSON_EXTRACT(data,'$.name') AS  name FROM user;

输出:

代码语言:javascript
复制
"李磊"

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

代码语言:javascript
复制
-- 带引号
SELECT loginInfo->"$.name" from UserLogin;
-- 不带引号
SELECT loginInfo->>"$.name" from UserLogin;
SELECT JSON_UNQUOTE(JSON_EXTRACT(data,'$.name')) AS  name FROM user;

输出:

代码语言:javascript
复制
李磊

列名中包含点符号

id

data

1

{ 'name': '李磊', 'no.' : '1234567' }

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

代码语言:javascript
复制
SELECT JSON_UNQUOTE(JSON_EXTRACT(data,'$."no."')) AS  mobile FROM users;
-- output 1234567

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

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

代码语言:javascript
复制
SELECT count(*), JSON_CONTAINS_PATH(data, 'one', '$.name') cp FROM user GROUP BY cp

json数据格式化 JSON_PRETTY

代码语言:javascript
复制
SELECT JSON_PRETTY(data) from user

返回格式化的json数据

代码语言:javascript
复制
{
  "name":"李磊",
  "age":28
}

计算json 字节数 JSON_STORAGE_SIZE

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

代码语言:javascript
复制
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 删除

本文分享自 作者个人站点/博客 前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • Json 类型简介
    • 一维json查询
      • 利用函数
      • 高级查询
      • 列过滤 和having
    • 二维json查询
      • 数组查询
        • 给Json中的字段添加索引
          • 引号JSON_UNQUOTE
            • 选择一个JSON字段
            • 从选择结果中删除双引号JSON_UNQUOTE,->和->>
            • 列名中包含点符号
          • 判断Json中是否有对应字段 JSON_CONTAINS_PATH
            • json数据格式化 JSON_PRETTY
              • 计算json 字节数 JSON_STORAGE_SIZE
                • 其他函数
                相关产品与服务
                数据库
                云数据库为企业提供了完善的关系型数据库、非关系型数据库、分析型数据库和数据库生态工具。您可以通过产品选择和组合搭建,轻松实现高可靠、高可用性、高性能等数据库需求。云数据库服务也可大幅减少您的运维工作量,更专注于业务发展,让企业一站式享受数据上云及分布式架构的技术红利!
                领券
                问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档