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
<https://stackoverflow.com/q/37485419/6805866> 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?
--
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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/jooq-user/61d8c94e-6509-4f1e-b9da-f258781d9d52o%40googlegroups.com.
