The introduction

I’m sure you’ve all encountered horizontal and vertical conversion problems in SQL. Let’s say there’s a student schedule and a major schedule,

Students < >

The name age
Student 1 19
Student 2 20

< Professional course >

Course name Elective students The teacher
Chinese language and literature Student 1 The language teacher
mathematics Student 1 Teachers of mathematics
Chinese language and literature Student 2 The language teacher
chemical Student 2 Chemistry teacher

If you wanted to find out what courses each student took, you would naturally combine the two tables, and select would result in something like this. This is not what we want, because student names are redundant.

Ordinary select combines results

Name Course Name Teacher Student 1 Chinese Chinese Teacher Student 1 Mathematics Mathematics teacher student 2 Chinese Chinese teacher Student 2 Chemistry Chemistry teacherCopy the code

But usually what we want is something like this. In other words, group by student 1’s course selection information to complete a horizontal and vertical transformation. And we might return the result to a Java entity class, so it’s best to do it in Json form. This allows us to wrap the course information in Json as an entity class.

Looking forward to the results

Name Course Information Student 1 [{course name: Chinese, Course teacher: Chinese}, {course name: Chinese, course teacher: Mathematics}] Student 2 [{course name: Chinese, course teacher: Chinese}, {course name: Chemistry, Course teacher: Chemistry}]Copy the code

The Result entity class contains the Course class. If it is the “ordinary select combined Result” class, then we have to combine it ourselves in Java, which is very troublesome. To get the “expected results” described above, you need to use the JSON function.

Result.java

// Student name
private String studentName;
// Course information
private Course course;
Copy the code

Course.java

/ / class name
private String courseName;
// Course teacher
private String courseTeacher;
Copy the code

Use jSON_AGG and json_build_object

Let’s start with the conclusion. SQL is as follows. The result is the desired result.

selectT1. Student name, json_AGg (json_build_object('courseName',t2.courseName,'courseTeacher',t2.courseTeacher)
   )
fromStudents table t1left joinSubject schedule T2onT1. Student name = T2. Student namegroup byT1. Name of studentCopy the code

What is a json_agg

Json_agg is an aggregation function, usually used in conjunction with group by, that aggregates values into a JSON array.

The function name parameter The return value
json_agg(expression) any json

Json_agg supports sorting, such as the SQL above can sort by courseName order by t2.coursename

selectT1. Student name, json_AGg (json_build_object('courseName',t2.courseName,'courseTeacher',t2.courseTeacher)
     order by t2.courseName -- Support sorting
   )
fromStudents table t1left joinSubject schedule T2onT1. Student name = T2. Student namegroup byT1. Name of studentCopy the code

What is a json_object_agg

Json_object_agg is a function that aggregates name/value pairs into a JSON object.

The function name parameter The return value
json_object_agg(name, value) (any, any) json

conclusion

If horizontal and vertical conversions are not implemented, you may need to manually assemble SQL results in Java for aggregation operations, or execute SQL twice. Using the jSON_AGg and json_build_object functions, you can wrap the result into JSON and put it directly into the entity class.