0%

Mysql数据库连表获取子表Json类型字段内容

实际开发过程中,总会遇到一些特定的问题,所以也需要一些特殊的解决方法,记录一次最近的SQL例子,A,B表连表查询,关系为一对多,但是查询结果只能为一条,也就是一条A的数据,对应B表很多条数据,但是只取B表最新的一条数据,然后再B表最新的数据中读取一个Json类型字段中的多个值,其中一个值又是一个json类型,还要更深层次获取json中的json中的属性值,再用as建立别名查询出来,你能想到几种方式呢?

我的第一反应不是用Sql语句解决,毕竟我觉得Sql太复杂了影响性能,我的思路:

一,数据肯定连表查,子查询查出B表最新的A表对应的数据,然后再和A表连表查,后台直接用JSONOBJECT来接收,注意使用自定义的TypeHandler,不然会类型解析错误,JSONOBJECT类型其实和Map很像,直接通过Get方法就可以,直接先获取到外层Json,再获取里层的Json,

1
2
3
4
5
6
7
8
9
JSONObject report = (JSONObject) JSONObject.parse(record.getReport());
JSONObject data = (JSONObject) report.get("data");
record.setStat((String) data.get("stat"));
JSONObject counter = (JSONObject) data.get("counter");
record.setThreads((Integer) counter.get("threads"));
record.setOpens((Integer) counter.get("opens"));
record.setErrors((Integer) counter.get("errors"));
record.setExcepts((Integer) counter.get("excepts"));
record.setRuns((Integer) counter.get("runs"));

是不是很简单,思路也很清晰,但是组长告诉我不能这样处理,因为外层的Json里面字段很多,而我这里查询的其实只是里层json的几个字段而已,这样查出来再做处理,并没有比直接Sql语句查询简单,要我用Sql搞定,没办法,领导最大,尝试第二种方法,

二,这就要使用到JSON_EXTRACT,配合Replace函数去掉值的引号,来看Sql,

注:JSON_EXTRACT直接获取Json类型字段,第一个参数是对应的Json类型的字段,第二个字段是获取里面的值,用$来获取值,该函数可以直接获取里外层的Json中的值;
REPLACE函数可以替换字符串多余的引号,否则后台查出来的值的内容是””\stat””,当然也可以后台遍历使用字符串的replace方法,但是都决定Sql搞定了,又何必还要后台再遍历一次呢。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
# 第一步,先分组查询B表最新的数据,
SELECT app_id, MAX( create_time ) max_time FROM B GROUP BY app_id

# 第二步,将第一步中B表查询语句定义为别名C,再和B表连表查,
SELECT
B.app_id AS badId,
#JSON_EXTRACT直接获取Json类型字段,用''和$来获取值,该函数可以直接获取里外层的Json中的值
#REPLACE函数可以替换字符串多余的引号,否则后台查出来的值的内容是""\stat"",当然也可以后台遍历使用字符串的replace方法
REPLACE(JSON_EXTRACT( B.report, '$.data.stat' ),'"','') AS stat,
JSON_EXTRACT( B.report, '$.data.counter.runs' ) AS runs,
JSON_EXTRACT( B.report, '$.data.counter.errors' ) AS ERRORS,
JSON_EXTRACT( B.report, '$.data.counter.excepts' ) AS excepts,
JSON_EXTRACT( B.report, '$.data.counter.opens' ) AS opens,
JSON_EXTRACT( B.report, '$.data.counter.threads' ) AS threads,
B.men
FROM
B,
( SELECT app_id, MAX( create_time ) max_time FROM B GROUP BY app_id ) C
WHERE
B.app_id = C.app_id
AND B.create_time = C.max_time
AND B.is_deleted = 0

#第三步,A,B表连表查询,
SELECT
*
FROM
A
LEFT JOIN (
SELECT
B.app_id AS badId,
REPLACE(JSON_EXTRACT( B.report, '$.data.stat' ),'"','') AS stat,
JSON_EXTRACT( B.report, '$.data.counter.runs' ) AS runs,
JSON_EXTRACT( B.report, '$.data.counter.errors' ) AS errors,
JSON_EXTRACT( B.report, '$.data.counter.excepts' ) AS excepts,
JSON_EXTRACT( B.report, '$.data.counter.opens' ) AS opens,
JSON_EXTRACT( B.report, '$.data.counter.threads' ) AS threads,
B.men
FROM
B,
( SELECT app_id, MAX( create_time ) max_time FROM B GROUP BY app_id ) C
WHERE
B.app_id = C.app_id
AND B.create_time = C.max_time
AND B.is_deleted = 0
) C ON A.app_id = C.badId
WHERE
A.is_deleted = 0
AND A.box_id = #{id}

查询结果:

image-20210329145606485

我觉得写的还算详尽了,也是一次很好的学习体验,JSON_EXTRACT也是第一次使用,发现意外的得劲,当然Sql别忘记添加索引,我这里用的联合索引,box_id和create_time,注意时间放在后面,不然会导致索引失效(范围索引后的索引失效),不懂的可以参看一下我的Mysql进阶-索引失效准则,如果有问题还请指教,谢谢。

----------本文结束感谢您的阅读----------