Jooq - Join tables and map to another pojo

Question

I have this classes:

class Student {
    String student_id;
    String name;
    List<GradeValue> grades;
}


class Grades {
    String id;
    String student_id;
    Long grade_value;
}


class GradeValue {
    Long grade_value;
}

I'm trying to use JOOQ to make a query that returns all students with all the grade values.

For this to happen I have to join the student and grades tables but then I should convert the grade to a grade value and I'm not sure how to do this.

List<Student> students = dsl.select(jsonObject(
    jsonEntry("id", STUDENT.STUDENT_ID),
    jsonEntry("name", STUDENT.NAME),
    jsonEntry("grades", field(
        select(jsonbObjectAgg(GRADES.GRADE_VALUE))
        .from(GRADES)
        .where(GRADES.STUDENT_ID.eq(STUDENT.STUDENT_ID))
    ))
)).from(STUDENT)
.fetchInto(Student.class);

Gives me this error:

Caused by: com.fasterxml.jackson.databind.exc.MismatchedInputException: Cannot deserialize instance of java.util.ArrayList<GradeValue> out of START_OBJECT token at [Source: (String)"{"id" : "1", "name" : "test", "grades" : {"grade_value": 10}}"; line: 1, column: 99]

Any ideias?

Thanks

Lukas Eder · Answer 1 · 2021-06-11T07:13:50.523

There are numerous problems with your code snippet:

Using `jsonArrayAgg()` instead of `jsonbObjectAgg()`

You didn't want to aggregate your grades into an object, I think, but into an array? Use jsonArrayAgg(), instead. See these manual sections for details:

Also, I suggest deciding for either using JSON or JSONB methods, not to mix them. But the way you wrote the query, that works for

class Student {
    String student_id;
    String name;
    List<Long> grades;
}

Since you're wrapping the grade in another class, you have to reflect that as well in your generated JSON structure:

List<Student> students = dsl.select(jsonObject(
    key("student_id").value(STUDENT.STUDENT_ID),
    key("name").value(STUDENT.NAME),
    key("grades").value(field(
        // See fix here
        select(jsonArrayAgg(jsonObject(
            key("grade_value").value(GRADES.GRADE_VALUE)
        )))
        .from(GRADES)
        .where(GRADES.STUDENT_ID.eq(STUDENT.STUDENT_ID))
    ))
)).from(STUDENT)
.fetchInto(Student.class);

Fetching into `STUDENT.class`

Perhaps just a typo, but you wrote:

.fetchInto(STUDENT.class)

You probably meant to write:

.fetchInto(Student.class)

Projecting the right attribute names

Your Student class has a student_id attribute, but you're projecting id:

jsonEntry("id", STUDENT.STUDENT_ID)

Better rename that attribute to id also, or project "student_id"

Aggregating empty sets

Note that JSON_ARRAYAGG() aggregates empty sets into NULL, not into an empty []. If that's a problem, use COALESCE()

@fitameh795: I don't know what possible new issue you might have encountered, but your using `jsonbObjectAgg()` doesn't make much sense. As you can see in your logs, you got a JSON object `{"grade_value": 10}` when you wanted to get a JSON array `[10]`. I'll be happy to walk you through any possible further issues, but I'll need a bit more information than "this doesn't work"... — Lukas Eder, Jun 02 '21 at 13:38

Jooq - Join tables and map to another pojo

1 Answers1

Using jsonArrayAgg() instead of jsonbObjectAgg()

Fetching into STUDENT.class

Projecting the right attribute names

Aggregating empty sets

Using `jsonArrayAgg()` instead of `jsonbObjectAgg()`

Fetching into `STUDENT.class`