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

引言

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

<学生>

<专业课>

如果需要找到每个学生分别选修了什么课程，理所当然要把两个表结合，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支持排序，比如上面的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 对象的函数。

结论

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