functions json object, json array in query builder using JOOQ

Question

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

Lukas Eder · Accepted Answer · 2021-04-13 15:57:20Z

3

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

answered Apr 13, 2021 at 15:57

Lukas Eder

223k138 gold badges729 silver badges1.6k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

RowlenZen Over a year ago

Thank you!! I've added in config jooqConfiguration.set(SQLDialect.POSTGRES); and everything becames okay. But function arrayAgg() worked without specifying the dialect, so I didn't think about this way

hujakhujak · Accepted Answer · 2021-04-13 15:58:04Z

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) {
        // ...
    }
}

Collectives™ on Stack Overflow

functions json object, json array in query builder using JOOQ

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related