CODEWITHSUNDEEP
javapostgresqljooq
RowlenZen
RowlenZen

Reputation: 23

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].

Upvotes: 2

Views: 2538

Answers (2)

hujakhujak
hujakhujak

Reputation: 28

If the goal of using json aggregation is to map result of query to embedded list of object such as

public class SomeDto {
    // ...other fields
    private List<InnerDto> innerDtoList;
}

Then you can try https://simpleflatmapper.org/

So in your example it will looks something like that

dependencies:

implementation 'org.simpleflatmapper:sfm-jooq:{version}'
implementation 'org.simpleflatmapper:sfm-jdbc:{version}'
implementation 'org.simpleflatmapper:sfm-map:{version}'

dto:

public class AuthorDto {

    @Key
    private Integer authorId;
    @Key
    private String name;

    private List<InfoDto> infos;
}

public class InfoDto {

    private String title;
    private String desc;
}

jooq query:

public List<AuthorDto> fetchAuthors(Integer myAuthorId) {

    final Select<?> query = defaultDSLContext
        .select(
            authorBookTable.AUTHOR_ID,
            authorTable.NAME,
            bookTable.TITLE.as("infos.title"), 
            bookTable.DESC.as("infos.desc")
        )
        .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));

        try (final ResultSet resultSet = query.fetchResultSet()) {
            
            return JdbcMapperFactory
                .newInstance()
                .newMapper(AuthorDto.class)
                .stream(resultSet)
                .collect(toList());

         } catch (final SQLException e) {
             // ...
         }

}

Or, instead of doing it everywhere, you can write generic method somewhere is base class or utils class. Like this:

protected <T> List<T> groupingFetch(final Select<?> query, final Class<? extends T> type) {

    try (final ResultSet resultSet = query.fetchResultSet()) {

        return JdbcMapperFactory
            .newInstance()
            .newMapper(type)
            .stream(resultSet)
            .collect(toList());
    
    } catch (final SQLException e) {
        // ...
    }
}

Upvotes: 0

Lukas Eder
Lukas Eder

Reputation: 221265

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 ...))

Upvotes: 3

Related Questions