PostgreSQL использует функции json_agg и json_build_object для решения проблемы горизонтального и вертикального преобразования комбинации нескольких таблиц SQL.

PostgreSQL

введение

Думаю, что все сталкивались с проблемой горизонтального и вертикального преобразования в SQL. Например, есть студенческий график и профессиональный график.

Имя возраст
студент 1 19
студент 2 20

название курса Студенты по выбору Учитель
язык студент 1 учитель языка
математика студент 1 учитель математики
язык студент 2 учитель языка
Химическая студент 2 Учитель химии

Если вам нужно узнать, какие курсы посещал каждый студент, само собой разумеющимся является объединение двух таблиц, и результат выбора выглядит следующим образом. Такой результат нам не нужен, потому что имя студента избыточно.

Обычный выбор комбинированных результатов

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

Но обычно мы хотим что-то вроде следующего. То есть сгруппируйте по информации о выборе курса студента 1, чтобы выполнить горизонтальное и вертикальное преобразование. И мы можем вернуть результат классу сущностей java, поэтому лучше быть в форме Json. Таким образом, мы можем инкапсулировать информацию о курсе в класс сущностей через Json.

ожидать результатов

姓名 课程信息
学生1 [{课程名：语文，课程教师：语文教师}，{课程名：数学，课程教师：数学教师}]
学生2 [{课程名：语文，课程教师：语文教师}，{课程名：化学，课程教师：化学教师}]

Этот класс сущностей может быть следующим, а класс сущностей Result содержит класс Course.Если это вышеупомянутый «результат комбинации обычного выбора», то мы должны объединить его в 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 может быть отсортирован по названию курса в порядке 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

В заключение

Если горизонтальное и вертикальное преобразование не реализовано, может потребоваться вручную собрать результаты SQL в java для операций агрегации или дважды выполнить SQL. Используйте функции json_agg и json_build_object, чтобы инкапсулировать результат в json и поместить его непосредственно в класс сущности.

Категории