How to convert "to_json()" PostgreSQL function in jOOQ?

Question

I am trying to convert following PostgreSQL query into jOOQ and can't seem to find adequate method for solving this issue. Here's the query:

SELECT b.book_id AS b_id, b.title, b.price, b.amount, b.is_deleted,
to_json(array_agg(DISTINCT aut.*)) as authors, 
to_json(array_agg(DISTINCT cat.*)) as categories 
FROM book b 
LEFT JOIN author_book AS ab ON b.book_id = ab.book_id
LEFT JOIN author AS aut ON ab.author_id = aut.author_id
LEFT JOIN category_book AS cb ON b.book_id = cb.book_id
LEFT JOIN category AS cat ON cb.category_id = cat.category_id
GROUP BY b_id ORDER BY b_id ASC;

So far this is what I came up with in jOOQ:

    dslContext
   .select(BOOK.BOOK_ID, BOOK.TITLE, BOOK.PRICE, BOOK.AMOUNT, BOOK.IS_DELETED, 
           DSL.val(DSL.jsonArray(DSL.arrayAggDistinct(AUTHOR.AUTHOR_ID), 
                                 DSL.arrayAggDistinct(AUTHOR.FIRST_NAME),
                                DSL.arrayAggDistinct(AUTHOR.LAST_NAME))
               ).cast(SQLDataType.JSON),                    
        DSL.val(DSL.jsonArray(DSL.arrayAggDistinct(CATEGORY.CATEGORY_ID), 
                              DSL.arrayAggDistinct(CATEGORY.NAME), 
                              DSL.arrayAggDistinct(CATEGORY.IS_DELETED))
            ).cast(SQLDataType.JSON)
    ).from(BOOK
            .leftJoin(AUTHOR_BOOK).on(BOOK.BOOK_ID.eq(AUTHOR_BOOK.BOOK_ID))
            .leftJoin(AUTHOR).on(AUTHOR_BOOK.AUTHOR_ID.eq(AUTHOR.AUTHOR_ID))
            .leftJoin(CATEGORY_BOOK).on(BOOK.BOOK_ID.eq(CATEGORY_BOOK.BOOK_ID))
            .leftJoin(CATEGORY).on(CATEGORY_BOOK.CATEGORY_ID.eq(CATEGORY.CATEGORY_ID))
    ).where(AUTHOR.AUTHOR_ID.eq(Long.valueOf(authorId))
    ).groupBy(BOOK.BOOK_ID).orderBy(BOOK.BOOK_ID.asc())

When I execute jOOQ code I get following exception:

Type class org.jooq.impl.JSONArray is not supported in dialect DEFAULT

I've googled this exception and found only this similar exception in this question. Is there any proper way-workaround to solve this conversion of PostgreSQL to_json function and combination of array_agg() which holds DISTINCT on all fields of given aut(hor)/cat(egory) table?

UPDATE: This is another way I used to write a query in jOOQ in NON-type-safe way:

dslContext.resultQuery(DBQueries.GET_ALL_BOOKS_BY_AUTHOR_ID, Long.valueOf(authorId)));

...and here is the code for DBQueries.GET_ALL_BOOKS_BY_AUTHOR_ID variable:

public class DBQueries {
//...
static String GET_ALL_BOOKS_BY_AUTHOR_ID = "SELECT b.book_id AS b_id, b.title, b.price, b.amount, b.is_deleted, " +
            "to_json(array_agg(DISTINCT aut.*)) as authors, to_json(array_agg(DISTINCT cat.*)) as categories " +            
            "FROM book b " + 
            "LEFT JOIN author_book AS ab ON b.book_id = ab.book_id " +
            "LEFT JOIN author AS aut ON ab.author_id = aut.author_id " +
            "LEFT JOIN category_book AS cb ON b.book_id = cb.book_id " +
            "LEFT JOIN category AS cat ON cb.category_id = cat.category_id " +
            "WHERE aut.author_id = :id " + 
            "GROUP BY b_id ORDER BY b_id ASC;"; 
}

BTW, I've updated my question for it to be clear how I've used resultQuery() method to achieve what I want. I'm asking how to achieve this in jOOQ in as much as possible type-safe way for I have even more complex query which I don't know if it's possible to achieve by NOT using PostgreSQL to_json() function. For instance I would like to create something like this in jOOQ like in section "04_create_functions.sql" of submitted link of bytefish.de web site.

get_image
--------------------------------------------------------------
 {"imageid" : 1, 
  "hash" : "a3b0c44", 
  "description" : "Some Description", 
  "created_on" : "2015-03-09T22:00:45.111", 
  "comments" : [{"commentid":1, "imageid":1, "text":"Awesome!", "createdon":"2015-03-09T22:58:47.783"},
                {"commentid":2, "imageid":1, "text":"This is just a second comment.", "createdon" : "2015-03-09T22:58:47.783"}], 
  "tags" : [{"tagid":1,"name":"Cool"}, 
            {"tagid":2,"name":"Berlin"}]}
(1 row)

I'm trying to do same thing for a REPORT for (book) ORDERS in which query I have 8 tables involved (I didn't post that query here as I am trying to solve more simple problem in jOOQ which is to create JsonArray).

Lukas Eder · Accepted Answer · 2020-06-09 14:51:20Z

1

Your specific errors

You're wrapping a Field expression (DSL.jsonArray()) in DSL.val(), which is meant for bind values. That doesn't work, and is the cause of your exception
However, you cannot yet turn an ordinary array into a JSON array by wrapping them that way. jOOQ doesn't know that what you mean by this is calling to_json behind the scenes. As of jOOQ 3.13, you will have to resort to using some plain SQL templating (see example below), or alternatively, you fetch an array without turning that into JSON in your SQL logic, and turn it into JSON only in your Java client logic.

Plain SQL templating example:

DSL.field("to_json({0})", JSON.class, DSL.arrayAggDistinct(AUTHOR.AUTHOR_ID))

jOOQ 3.14 JSON support

Note that jOOQ 3.14 will support much more JSON functionality via standard SQL JSON API, and SQL Server's FOR JSON clause:

And:

SQL Server FOR JSON syntax support and emulation

edited Jun 9, 2020 at 14:51

answered Jun 8, 2020 at 15:14

Lukas Eder

223k138 gold badges729 silver badges1.6k bronze badges

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

5 Comments

NikolaS Over a year ago

If I understood you correctly you are suggesting to use "plain SQL templating" to build final result by using something as named/inlined parameters and take out AUTHORS and CATEGORIES as plain ARRAY of values ORR to use something like resultQuery(String plainSQL, Object.. bindings) method where I would pass plain written SQL query and (in this case) value of authorId (by which I am performing search) in ONE go (but that is NOT type-safe way)?

NikolaS Over a year ago

I've updated my question to be more clear about what I'm trying to achieve.

Lukas Eder Over a year ago

@NikolaS: No, not for the entire query, just for that function call! I'll update my answer

NikolaS Over a year ago

@Lukas_Eder: I've listened to your advice, looked through documentation and adjusted my jOOQ code by following your and Docs example. Solution can be see in my own answer and if you have any suggestions/comments please let me know. Thanks once again. :)

Lukas Eder Over a year ago

@NikolaS: Perfect, thanks for documenting your own solution here, as an answer. People should do this more often!

NikolaS · Accepted Answer · 2020-06-10 07:53:42Z

1

As @Lukas_Eder pointed out to solve this issue it's needed to create JSON object via plain SQL templating like in following example:

DSL.field("to_json({0})", JSON.class, DSL.arrayAggDistinct(AUTHOR.AUTHOR_ID))

I've read bit more about above mentioned method in documentation and got idea how to modify method for my own need (in order to be equal to one posted in question in PostgreSQL syntax):

DSL.field( "to_json(array_agg(DISTINCT author.*))", JSON.class, DSL.arrayAgg(AUTHOR.AUTHOR_ID) ).as("authors")

answered Jun 10, 2020 at 7:53

NikolaS

5332 gold badges9 silver badges25 bronze badges

1 Comment

Lukas Eder Over a year ago

In the last example, you don't need to pass the DSL.arrayAgg() expression, because you don't have any template placeholder to match it to (e.g. {0}). However, you can still pass the AUTHOR table if you want, e.g. DSL.field("to_json(array_agg(distinct {0}.*))", JSON.class, AUTHOR)

Collectives™ on Stack Overflow

How to convert "to_json()" PostgreSQL function in jOOQ?

2 Answers 2

Your specific errors

jOOQ 3.14 JSON support

5 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Your specific errors

jOOQ 3.14 JSON support

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related