title: How to convert "to_json()" PostgreSQL function in jOOQ?
body: <p>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:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>So far this is what I came up with in jOOQ:</p>
  
  <pre><code>    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())
  </code></pre>
  
  <p>When I execute jOOQ code I get following exception:</p>
  
  <blockquote>
    <p>Type class org.jooq.impl.JSONArray is not supported in dialect DEFAULT</p>
  </blockquote>
  
  <p>I've googled this exception and found only this similar exception in <a href="https://stackoverflow.com/q/37485419/6805866">this</a> question. Is there any proper way-workaround to solve this conversion of PostgreSQL <code>to_json</code> function and combination of array_agg() which holds DISTINCT on all fields of given aut(hor)/cat(egory) table? </p>
  
  <p><strong>UPDATE</strong>:
  This is another way I used to write a query in jOOQ in NON-type-safe way:</p>
  
  <pre><code>dslContext.resultQuery(DBQueries.GET_ALL_BOOKS_BY_AUTHOR_ID, Long.valueOf(authorId)));
  </code></pre>
  
  <p>...and here is the code for <code>DBQueries.GET_ALL_BOOKS_BY_AUTHOR_ID</code> variable:</p>
  
  <pre><code>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;"; 
  }
  </code></pre>
  
  <p>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 <code>to_json()</code> function. For instance I would like to create something like <a href="https://bytefish.de/blog/postgresql_json/" rel="nofollow noreferrer">this</a> in jOOQ like in section "04_create_functions.sql" of submitted link of bytefish.de web site.</p>
  
  <pre><code>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)
  </code></pre>
  
  <p>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).</p>