Fetching One to Many associations with JooQ

Question

I'm trying to deserialise a one to many association with JooQ (without code generation) as per this post.

Here are my target classes.

public class Author {
    private Long id;
    private String name;
    private List<Book> books;
}

public class Book {
   private String name;
}

My JooQ query is as follows:

dslContext
            .select(table("authors").asterisk(),
                    field(
                      select(jsonArrayAgg(
                                   jsonObject(
                                       jsonEntry("name", field("books.name")))))
                      .from(table("books"))
                      .join(table("authors"))                        
                      .on(field("books.author_id").eq(field("authors.id")))
                      .where(field("emails.collection_case_id")
                        .eq(field("collection_cases.id")))
                    ).as("books"))
            .from(table("authors"))
            .where(trueCondition())
            .fetchInto(Author.class);

The jsonObject() method does not work as expected for me. The generated SQL statement looks something like this:

select authors.*, (select json_agg(json_build_object(?, books.name)) from books join authors ...

The translated postgres query has not properly replaced the key attribute of json_build_object and this results in SQL exception.

PS: I'm using JooQ 3.14.0 with postgres 11.5

Why are you not using the code generator (probably not related, but for context)? What's the exception and stack trace that you're getting? — Lukas Eder, Nov 04 '20 at 14:14
@LukasEder I'm getting SQL Grammer Exception: org.postgresql.util.PSQLException: ERROR: could not determine data type of parameter $1 The postgres query works if i manually update json_build_object(?, books.name) with json_build_object('name', books.name) — Vijith mv, Nov 04 '20 at 14:20
@LukasEder We are trying out JooQ for a new functional piece and doesn't really want to disturb the existing JPA entities just for this experiment. Thats the reason we didnt really used the code generation. — Vijith mv, Nov 04 '20 at 14:22
@Vijithmv: Regarding the code generation, there shouldn't be any "disturbance". You can even [use JPA entities as a source for your code generation](https://www.jooq.org/doc/latest/manual/code-generation/codegen-jpa/). You'll get *much more* out of jOOQ with code generation. I'll look into this issue. A workaround is to use `DSL.inline("name")` to avoid the bind variable. — Lukas Eder, Nov 04 '20 at 14:24
@Vijithmv: I'm afraid I can't seem to reproduce this. What JDBC driver version are you using? — Lukas Eder, Nov 04 '20 at 14:42
@LukasEder I can see spring-boot-starter-jdbc:jar:2.3.1.RELEASE in my maven dependency tree — Vijith mv, Nov 04 '20 at 14:53
I mean your PostgreSQL JDBC driver. E.g. `org.postgresql:postgresql:??` — Lukas Eder, Nov 04 '20 at 15:05
@Vijithmv: OK, while that's not the latest driver version, I can't reproduce it with that version either. Last thing I can think of is, maybe your PG setup is different? What do you get from running this query? `select count(distinct specific_name) from information_schema.routines where routine_name = 'json_build_object'` — Lukas Eder, Nov 04 '20 at 15:51

score 1 · Accepted Answer · answered Nov 04 '20 at 15:58

While I can't reproduce this issue on my side with various PostgreSQL server and JDBC driver versions, the simple workaround here is to use DSL.inline(String) to prevent jOOQ's generating a bind variable for the json_build_object() function argument:

jsonEntry(inline("name"), field("books.name"))

Fetching One to Many associations with JooQ

1 Answers1