CODEWITHSUNDEEP
javajsonpostgresqlaggregatejooq
NikolaS
NikolaS

Reputation: 523

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

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

Upvotes: 1

Views: 2019

Answers (2)

NikolaS
NikolaS

Reputation: 523

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

Upvotes: 1

Lukas Eder
Lukas Eder

Reputation: 221275

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:

Upvotes: 1

Related Questions