Usage and Possible configuration of multiset and multisetAgg

503 views
Skip to first unread message

Yanko Bahchevanov

unread,
Jan 24, 2022, 12:29:18 PM1/24/22
to jOOQ User Group
Hi all,

I am a bit new to JOOQ and the whole concept it brings and have been reading and exploring a lot. One thing I am having trouble with (maybe I am not utilizing it well enough not sure) is using multiset and multisetAgg. 

I scoured the JOOQ documentatation and every question and issue I could find that was related to those 2, but couldn't find my answer. 

My stack is Vert.x + JOOQx (wrapper for JOOQ for Vert.x) + JOOQ (v3.16.2) + Kotlin + Postgres.

So my issue is this: 

Code: 

val casesQuery = jooq.dsl()
.select(
     CASES.CASE_ID,
     CASES.NAME,
     CASES.DESCRIPTION,
     multisetAgg(
                 DOCUMENTS.DOCUMENT_ID,
                 DOCUMENTS.NAME,
                 DOCUMENTS.DESCRIPTION
         ).`as`("documents").convertFrom {
                        it?.map {mapping(::Document)}
                  })
.from(CASES)
.leftJoin(REF_CASES_DOCUMENTS)
.on(REF_CASES_DOCUMENTS.CASE_ID.eq(CASES.CASE_ID))
.leftJoin(DOCUMENTS)                      .on(REF_CASES_DOCUMENTS.DOCUMENT_ID.eq(DOCUMENTS.DOCUMENT_ID))
.where(CASES.CASE_ID.eq(id))
.groupBy(CASES.CASE_ID, CASES.NAME, CASES.DESCRIPTION)
val cases = jooq.execute(casesQuery, DSLAdapter.fetchMany(casesQuery.asTable(), Case::class.java)).await()

This generates the following query: 

select
  "public"."cases"."case_id",
  "public"."cases"."name",
  "public"."cases"."description",
  jsonb_agg(jsonb_build_array("public"."documents"."document_id", "public"."documents"."name", "public"."documents"."description")) as "documents"
from "public"."cases"
  left outer join "public"."ref_cases_documents"
    on "public"."ref_cases_documents"."case_id" = "public"."cases"."case_id"
  left outer join "public"."documents"
    on "public"."ref_cases_documents"."document_id" = "public"."documents"."document_id"
where "public"."cases"."case_id" = 'ab49dc80-b787-48d4-ae7a-9cc455b3d08a'
group by "public"."cases"."case_id", "public"."cases"."name", "public"."cases"."description"


Im trying to map the inner Object to Document data class, but the query gets generated with "jsonb_build_array" instead of "jsonb_build_object" so I get an InvalidCastExcetion.

Im using custom data classes with JPA Column annotations and not using DAOs or POJOs generated from JOOQ. Its worth saying that underneath the wrappers plain JOOQ is used. 

All the documentation and posts I saw point to a generated SQL with jsonb_build_object, but this is not true in my case. Is it some sort of configuration or inferrence from my setup or something? 

This is probably the only problem I need to solve, otherwise JOOQ looks absolutely awesome, so good job to the team that is developing it!

Lukas Eder

unread,
Jan 24, 2022, 2:36:39 PM1/24/22
to jOOQ User Group
Hi Yanko,

On Mon, Jan 24, 2022 at 6:29 PM Yanko Bahchevanov <yanko.ba...@gmail.com> wrote:
Im using custom data classes with JPA Column annotations and not using DAOs or POJOs generated from JOOQ. Its worth saying that underneath the wrappers plain JOOQ is used. 

I think that's where the problem is. If you're using a third party library to execute jOOQ queries, then you can't profit from those advanced mapping features, which are implemented in jOOQ itself, not in any third parties. If you use jOOQ to execute SQL directly via JDBC or R2DBC, you can use the MULTISET, ROW operators out of the box. If you want to extract the SQL string and execute it elsewhere (yourself or via a third party), then you'll have to wire the generated JSON / XML back to jOOQ data structures yourself. DSLContext::fetchFromJSON could be of use, but it won't be able to access the projected type information as easily, and you don't get any type safety.

There might be public API in a future jOOQ release to help construct the jOOQ Result/Record[N] type hierarchy from nested JSON / XML data structures (see https://github.com/jOOQ/jOOQ/issues/12012), but irrespective of such low level improvements, it will always be much more interesting to let jOOQ execute your query for you, rather than any third party.
 
All the documentation and posts I saw point to a generated SQL with jsonb_build_object, but this is not true in my case. Is it some sort of configuration or inferrence from my setup or something?

Can good point, that implementation has changed as a part of a series of bug fixes. It turned out that, since we can't use the JSON object key names anyway for this serialisation (ambiguous key names, lack of ordering), we might as well just use JSON arrays instead, which at least guarantee ordering, and use less space. Having said so, you shouldn't see the examples in the manual as formal specification of what jOOQ does to get MULTISET to work. This might change again in the future, as we run into some caveats, and there are numerous ones. Here's some insight:
 
This is probably the only problem I need to solve, otherwise JOOQ looks absolutely awesome, so good job to the team that is developing it!

You won't look back, especially not once you get MULTISET to work! 

Yanko Bahchevanov

unread,
Jan 24, 2022, 4:34:31 PM1/24/22
to jOOQ User Group
Hey Lukas,

Appreciate the answer! By your explanation if I switch to using a pure JDBC driver implementation, will it work with custom data classes or will I have to still use the POJOs generated from JOOQ itself? The main reason why I did not want to use those was, because I couldn't get it to serialize easily to a pure JSON data structure for HTTP output. My case is very simple request-> query -> response, so being able to just get the result and serialize it to JSON easily without much boilerplate is a bit important to me. I can still use the POJOs even without changing the driver if it will fix the issue, not using them was a conscoius decision on my side, not a limitation or anything. These third parties that I am using are more of a bridge for Vert.x reactive SQL client than anything else, so they dont really change anything and still use the JOOQ methods underneath. 

This is the line after the query declaration for a bit of context: 


val cases = jooq.execute(casesQuery, DSLAdapter.fetchMany(casesQuery.asTable(), Case::class.java)).await()

And this is the "bridge" that I am using, if it can help shed some light on the problem. 


Again thank you very much for taking the time to look into this! You rock! My bad on the private message, first time using Google Groups.

Lukas Eder

unread,
Jan 24, 2022, 4:43:49 PM1/24/22
to jOOQ User Group
On Mon, Jan 24, 2022 at 10:34 PM Yanko Bahchevanov <yanko.ba...@gmail.com> wrote:
Hey Lukas,

Appreciate the answer! By your explanation if I switch to using a pure JDBC driver implementation, will it work with custom data classes or will I have to still use the POJOs generated from JOOQ itself?

 
The main reason why I did not want to use those was, because I couldn't get it to serialize easily to a pure JSON data structure for HTTP output. My case is very simple request-> query -> response, so being able to just get the result and serialize it to JSON easily without much boilerplate is a bit important to me.

Well, there's no stopping you from going the direct path:

MULTISET etc is for Java consumers. If your consumers prefer JSON, why the intermediate step?
 
I can still use the POJOs even without changing the driver if it will fix the issue, not using them was a conscoius decision on my side, not a limitation or anything. These third parties that I am using are more of a bridge for Vert.x reactive SQL client than anything else, so they dont really change anything and still use the JOOQ methods underneath. 

This is the line after the query declaration for a bit of context: 

val cases = jooq.execute(casesQuery, DSLAdapter.fetchMany(casesQuery.asTable(), Case::class.java)).await()

And this is the "bridge" that I am using, if it can help shed some light on the problem. 


I'm not too acquainted with the inner workings of that third party library, but a quick search shows that they're extracting SQL from jOOQ:

Which is probably done to bind to a non-JDBC API. Hence, I seriously doubt they've implemented multiset support. But why not ask the author(s) about their own opinions on what the limitations are?

Yanko Bahchevanov

unread,
Jan 25, 2022, 7:50:49 AM1/25/22
to jOOQ User Group
Thank you for the tips! Have a good one!
Reply all
Reply to author
Forward
0 new messages