Postgresql – How can I select a Long[] field into my pojo from a lateral join using arrayAgg()?
Here is an example of the query I'm trying to execute and the POJO I'm trying to populate. public Optional<TeacherWithStudents> getTeachersWithStudentsForStudentId(long studentId) { return ctx().select( TEACHER.ID, TEACHER.NAME, DSL.field("student.ids", STUDENT_TEACHER_MAPPING.STUDENT_ID.getDataType().getArrayType())) .from(TEACHER) .leftJoin(DSL.lateral( DSL.select(DSL.arrayAgg(STUDENT_TEACHER_MAPPING.STUDENT_ID).as("ids")) .from(STUDENT_TEACHER_MAPPING) .where(STUDENT_TEACHER_MAPPING.TEACHER_ID.eq(TEACHER.ID))).as("student")) .on(DSL.condition(true)) .limit(1) .fetchOptionalInto(TeacherWithStudents.class); } // TeacherWithStudents…