【SQL编程】MySQL 5.7.28 版本使用 SQL 直接解析 JSON 字符串(判断是否是合法JSON类型+文本深度+文本长度+值类型+keys获取+值获取+不同深度数据获取)

本文涉及的产品
云数据库 RDS MySQL Serverless,0.5-2RCU 50GB
简介: 【SQL编程】MySQL 5.7.28 版本使用 SQL 直接解析 JSON 字符串(判断是否是合法JSON类型+文本深度+文本长度+值类型+keys获取+值获取+不同深度数据获取)

1. 版本说明

SELECT @@version;
-- 5.6.35-log 不支持 JSON 解析相关函数
-- 5.7.28 支持

2. 测试代码

准备阶段

建表

CREATE TABLE `tb_test_json` (
  `id` varchar(64) NOT NULL COMMENT '主键',
  `json_str` varchar(255) DEFAULT NULL COMMENT 'JSON字符串',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

录入数据

INSERT INTO `test`.`tb_test_json` ( `id`, `json_str` )
VALUES
  ( '001', '{    \"age\": 10,    \"gender\": \"male\",    \"hobby\": [        {            \"describe\": \"健身时听摇滚乐,身心愉悦\",            \"type\": \"music\"        },        {            \"describe\": \"偶尔垂钓,纯属娱乐\",            \"type\": \"fishing\"        }    ],    \"name\": \"杰森\"}' );
INSERT INTO `test`.`tb_test_json` ( `id`, `json_str` )
VALUES
  ( '002', '{    \"age\": 20,    \"gender\": \"female\",    \"hobby\": [        {            \"describe\": \"买买买,钱包被掏空\",            \"type\": \"shopping\"        },        {            \"describe\": \"东南西北,美食在心中\",            \"type\": \"food\"        }    ],    \"name\": \"贝比\"}' );
INSERT INTO `test`.`tb_test_json` ( `id`, `json_str` )
VALUES
  ( '003', '{    \"age\": 30,    \"gender\": \"female\",   \"name\": \"汤姆\"}' );

测试说明 JSON相关的函数,大部分我们看名字就知道函数的作用:

2.1 判读是否是合法的JSON类型

SELECT json_valid( 'hello' ), json_valid( '"hello"' );

2.2 JSON文本深度

SELECT id, json_depth( json_str ) FROM tb_test_json;

2.3 JSON文本长度

SELECT id, json_length( json_str ) FROM tb_test_json;

2.4 JSON值类型

SELECT id, json_type( json_str ) FROM tb_test_json;

2.5 JSON的keys

SELECT id, json_keys( json_str ) FROM tb_test_json;

2.6 JSON值获取

SELECT
  id,
  json_extract( json_str, '$.name' ) AS name0,
  json_extract( json_str, '$.age' ) AS age0,
  json_str ->> '$.name' AS name1,
  json_str ->> '$.age' AS age1,
  json_str -> '$.name' AS name2,
  json_str -> '$.age' AS age2 
FROM
  tb_test_json;

2.7 JSON数据解析

单箭头获取值 双箭头获取字符串

-- 【可以】获取结果
SELECT * FROM tb_test_json WHERE json_str -> '$.age' = 20;
-- 【可以】获取结果
SELECT * FROM tb_test_json WHERE json_str ->> '$.age' = '20';
-- 【不可以】获取结果
SELECT * FROM tb_test_json WHERE json_str -> '$.age' = '20';

数据筛选加解析【不同深度】

SELECT
  id,
  json_extract( json_str, '$.name' ) AS name0,
  json_extract( json_str, '$.age' ) AS age0,
  json_extract( json_str, '$.hobby[0].type' ) AS hobby0,
  json_str ->> '$.name' AS name1,
  json_str ->> '$.age' AS age1,
  json_str ->> '$.hobby[0].type' AS hobby1,
  json_str -> '$.name' AS name2,
  json_str -> '$.age' AS age2,
  json_str -> '$.hobby[0].type' AS hobby2 
FROM
  tb_test_json 
WHERE
  json_str ->> '$.name' = '杰森' OR json_str -> '$.age' = 30 OR json_str -> '$.hobby[0].type' = 'shopping';

目录
相关文章
|
21天前
|
SQL 关系型数据库 MySQL
mysql一条sql查询出多个统计结果
mysql一条sql查询出多个统计结果
14 0
|
12天前
|
存储 关系型数据库 MySQL
MySQL引擎对决:深入解析MyISAM和InnoDB的区别
MySQL引擎对决:深入解析MyISAM和InnoDB的区别
28 0
|
8天前
|
SQL 关系型数据库 数据库
【后端面经】【数据库与MySQL】SQL优化:如何发现SQL中的问题?
【4月更文挑战第12天】数据库优化涉及硬件升级、操作系统调整、服务器/引擎优化和SQL优化。SQL优化目标是减少磁盘IO和内存/CPU消耗。`EXPLAIN`命令用于检查SQL执行计划,关注`type`、`possible_keys`、`key`、`rows`和`filtered`字段。设计索引时考虑外键、频繁出现在`where`、`order by`和关联查询中的列,以及区分度高的列。大数据表改结构需谨慎,可能需要停机、低峰期变更或新建表。面试中应准备SQL优化案例,如覆盖索引、优化`order by`、`count`和索引提示。优化分页查询时避免大偏移量,可利用上一批的最大ID进行限制。
32 3
|
11天前
|
存储 JSON JavaScript
「Python系列」Python JSON数据解析
在Python中解析JSON数据通常使用`json`模块。`json`模块提供了将JSON格式的数据转换为Python对象(如列表、字典等)以及将Python对象转换为JSON格式的数据的方法。
27 0
|
14天前
|
SQL 存储 关系型数据库
【MySQL实战笔记】02.一条SQL更新语句是如何执行的-2
【4月更文挑战第5天】两阶段提交是为确保`redo log`和`binlog`逻辑一致,避免数据不一致。若先写`redo log`, crash后数据可能丢失,导致恢复后状态错误;若先写`binlog`,crash则可能导致重复事务,影响数据库一致性。一天一备相较于一周一备,能缩短“最长恢复时间”,但需权衡额外的存储成本。
16 1
|
21天前
|
SQL 关系型数据库 MySQL
【MySQL】慢SQL分析流程
【4月更文挑战第1天】【MySQL】慢SQL分析流程
|
24天前
|
canal 消息中间件 关系型数据库
【分布式技术专题】「分布式技术架构」MySQL数据同步到Elasticsearch之N种方案解析,实现高效数据同步
【分布式技术专题】「分布式技术架构」MySQL数据同步到Elasticsearch之N种方案解析,实现高效数据同步
73 0
|
6天前
yolo-world 源码解析(六)(2)
yolo-world 源码解析(六)
16 0
|
6天前
yolo-world 源码解析(六)(1)
yolo-world 源码解析(六)
8 0
|
6天前
yolo-world 源码解析(五)(4)
yolo-world 源码解析(五)
14 0

推荐镜像

更多