PostgreSQL 使用json_agg跟json_build_object函数,解决SQL多表结合的横竖转换问题

·  阅读 3842

引言

相信大家都遇到过SQL中的横竖转换问题。比如说有个学生表跟专业课表,

<学生>

姓名 年龄
学生1 19
学生2 20

<专业课>

课程名 选修学生 任课教师
语文 学生1 语文教师
数学 学生1 数学教师
语文 学生2 语文教师
化学 学生2 化学教师

如果需要找到每个学生分别选修了什么课程,理所当然要把两个表结合,select的结果是下面这样的。这样的结果不是我们想要的,因为学生姓名是冗余的。

普通select结合结果

姓名  课程名 任课教师
学生1 语文 语文教师
学生1 数学 数学教师
学生2 语文 语文教师
学生2 化学 化学教师
复制代码

但通常我们想要的是下面这样的。也就是把学生1的选课信息group by一下,完成一个横竖转化。而且我们可能会把结果返回给一个java实体类,所以最好是Json形式。这样我们就能把课程信息通过Json封装成一个实体类。

期待结果

姓名 课程信息
学生1 [{课程名:语文,课程教师:语文教师},{课程名:数学,课程教师:数学教师}]
学生2 [{课程名:语文,课程教师:语文教师},{课程名:化学,课程教师:化学教师}]
复制代码

这个实体类可能是下面这样,Result实体类又包含了Course类,如果是上述的“普通select结合结果”,那么我们就必须在java中自己组合,非常麻烦。要得到上述的“期待结果”,则需要使用json函数了。

Result.java

// 学生姓名
private String studentName;
// 选课信息
private Course course;
复制代码

Course.java

// 课程名
private String courseName;
// 课程教师
private String courseTeacher;
复制代码

使用json_agg跟json_build_object

先看结论。SQL如下。结果便是上述的期待结果。

select
   t1.学生姓名
   , json_agg(
     json_build_object('courseName',t2.courseName,'courseTeacher',t2.courseTeacher)
   )
from
   学生表 t1
left join
   专业课表 t2
on t1.学生姓名 = t2.学生姓名
group by
  t1.学生姓名
复制代码

什么是json_agg

json_agg是一个聚合函数,通常跟group by配合使用,将值聚集成一个 JSON 数组。

函数名 参数 返回值
json_agg(expression) any json

json_agg支持排序,比如上面的sql可以按照课程名排序order by t2.courseName

select
   t1.学生姓名
   , json_agg(
     json_build_object('courseName',t2.courseName,'courseTeacher',t2.courseTeacher)
     order by t2.courseName --支持排序
   )
from
   学生表 t1
left join
   专业课表 t2
on t1.学生姓名 = t2.学生姓名
group by
  t1.学生姓名
复制代码

什么是json_object_agg

json_object_agg是将名字/值对聚集成一个 JSON 对象的函数。

函数名 参数 返回值
json_object_agg(name, value) (any, any) json

结论

如果不实现横竖转换,可能需要java中手动组装SQL结果进行聚合操作,或者二次执行sql。使用json_agg跟json_build_object函数可以将结果封装成json,直接放进实体类。

分类:
后端
标签:
分类:
后端
标签:
收藏成功!
已添加到「」, 点击更改