Thanks for your message. I've seen it also on Stack Overflow and will reply there: https://stackoverflow.com/q/62222816/521799
On Mon, Jun 8, 2020 at 10:51 AM Nikola Stevanović <[email protected]> wrote: > 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 > <https://groups.google.com/d/msgid/jooq-user/61d8c94e-6509-4f1e-b9da-f258781d9d52o%40googlegroups.com?utm_medium=email&utm_source=footer> > . > -- 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/CAB4ELO4Q7FZrzhP9JoHe%3Dq9nr6pM%3D%3DE3hAaPcCZaMeF%3D3YdqLQ%40mail.gmail.com.
