Search code examples
javapostgresqljooq

functions json object, json array in query builder using JOOQ

I started to use jooq not far ago. I like it, but I met a problem with building query.

My tools are Postgresql, java 11, Springboot. I want to get some data from database without extra rows.

I have two tables and many-to-many relationship between them. Easily with sql:

SELECT author_book.author_id, author.name, json_agg(json_build_object(
    'title', book.title,
    'desc', book.desc)) as info
FROM author_book
        JOIN book on book.id = author_book.book_id
        JOIN author on author.id = author_book.author_id
WHERE author_id = 1687
group by author_id, author.name;

Then I got this:

id name info
id author [{ "title" : "title_1", "desc" : "desc_1"}, {"title" : "title_2", "desc" : "desc_2"}, ...]

The thing is combining fields {book.title, book.desc} in one object and collect into an array. Then in java we would easy parse such object to inner models.

I tried such query in jooq:

Integer myAuthorId;

defaultDSLContext.select(
                authorBookTable.AUTHOR_ID,
                authorTable.NAME,
                DSL.jsonArrayAgg(DSL.jsonbObject(
                        key("title").value(bookTable.TITLE),
                        key("desc").value(bookTable.DESC)))              
                        .as("info"))
                .from(authorBookTable)
                .join(bookTable).on(bookTable.ID.eq(authorBookTable.BOOK_ID))
                .join(authorTable).on(authorTable.ID.eq(authorBookTable.AUTHOR_ID))
                .where(authorBookTable.AUTHOR_ID.eq(myAuthorId))
                .groupBy(authorBookTable.AUTHOR_ID,
                        authorTable.NAME)
                .fetchOne()

My imports:

import com.google.gson.Gson;
import org.jooq.impl.DSL;
import org.jooq.impl.DefaultDSLContext;
import org.springframework.stereotype.Repository;

import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.Set;

import static org.jooq.impl.DSL.key;

Unfotunately i got an error:

SQL [select "public"."author_book"."author_id", "public"."author"."name",
json_arrayagg(json_object(key ? value "public"."book"."title", key ? value "public"."book"."desc")) "info" 
from "public"."author_book" 
join "public"."book" on "public"."book"."id" = "public"."author_book"."book_id" 
join "public"."author" on "public"."author"."id" = "public"."author_book"."author_id" 
where "public"."author_book"."author_id" = ? 
group by "public"."author_book"."author_id", "public"."author"."name"]; 
ERROR: syntax error at or near "$1"
  Position: 104

An Example how to use jsonObject() I've seen here json-object-function and here blog.jooq. I will be happy to resolve this with your tips.

So what way to resolve this? Why does it not work?

Actually, I've found a solution using arrayAgg() and array() instead jsonArrayAgg() and jsonObject(), but I think it is not a best practise, because then we get our fields title and desc as usually get element in array by [0], [1].

Solution

  • It doesn't look like you've correctly configured jOOQ to use SQLDialect.POSTGRES in your defaultDSLContext instance, otherwise it wouldn't generate standard SQL/JSON syntax like:

    json_arrayagg(json_object(key ? value ...))