How to convert “to_json()” PostgreSQL function in jOOQ?

[Nikola Stevanović]

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?

[Lukas Eder]

Thanks for your message. I've seen it also on Stack Overflow and will reply there:

https://stackoverflow.com/q/62222816/521799

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/61d8c94e-6509-4f1e-b9da-f258781d9d52o%40googlegroups.com.