jOOQ Multiset Operator returns nested JsonArray on query

Question

For context, we are using jooq 3.17 and vertx-jooq 6.5.5 inside Quarkus. As a consequence, everything is a Mutiny stream. We cannot use blocking operations (like fetch()).

As proposed in the official tutorial, we tried to utilize the jOOQ Multiset Operator. For that, we are using the flowing code:

ReactiveMutinyQueryExecutor executor = ...

executor
        .query(dslContext -> dslContext
            .select(TABLE_A.TABLE_C_ID,
                TABLE_C.NAME,
                DSL
                    .multiset(DSL
                        .select(TABLE_B.TABLE_C_ID, TABLE_B.NAME, TABLE_B.PARAMETER_TYPE)
                        .from(TABLE_B)
                        .where(TABLE_B.TABLE_A_ID.eq(TABLE_A.TABLE_A_ID)))
                    .as(TABLE_BS))
            .from(TABLE_A)
            .join(TABLE_C)
            .using(TABLE_C.TABLE_C_ID)
            .where(TABLE_A.TABLE_C_ID.eq(tableCId)))
        .map(queryResult -> queryResult.get(TABLE_BS, MyPojo.class))

When calling get an exception occurs:

org.jooq.exception.DataTypeException: Cannot convert from [[8,"a","NUMBER"],[9,"b","NUMBER"]] (class io.vertx.core.json.JsonArray) to class com.MyPojo

Our goal is to map the multiset to a MyPojo.

 .convertFrom(MyClass::toMyPojo));

The suggested convertFrom() method does not have any effect, we don't call fetch (as stated above).

However, when calling queryResult.get(TABLE_BS, String.class), the result is a nested JSONArray:

[[8,"a","NUMBER"],[9,"b","NUMBER"]]

where we were hoping to receive at least a nested JsonObject. So in our example two JsonObjects inside a JsonArray.

Answer 1

Execution with jOOQ directly

This article on the jOOQ blog explains why you should execute jOOQ queries with jOOQ, not with third parties. One of the reasons is that the type safe MULTISET (and ROW) deserialisation relies on knowledge about the internal SQL/JSON serialisation format implemented by jOOQ. jOOQ supports this deserialisation out of the box on JDBC (blocking) or R2DBC (non-blocking).

Use internal API to convert the JSON to a Result

You could use internal API from jOOQ to convert the JSON data structure to a org.jooq.Result, which can then be used to transform the nested record structure to your own MyPojo types. Given that this is about internal APIs, I cannot say how this works here on Stack Overflow, as the answer might be outdated already in a month.

But you could get it to work, or even implement it in that third party vert.x-to-jOOQ bridge that you're probably using.

In any case, there's a pending feature request to offer this functionality as public API: #12012, so future third parties can offer this functionality as well.

Why an array of arrays?

There are various reasons why the preferred serialisation format uses arrays of arrays, rather than arrays of objects, including:

• It's less verbose, thus less data transfer (redundancy on each row!)
• Column access by index is also faster than column access by string
• Column names can be ambiguous (e.g. SELECT 1 AS a, 2 AS a, t.a FROM t), so it's not reliable anyway

Note that the exact serialisation format isn't documented, so it might change as well between releases, e.g. when there's a limitation in the SQL/JSON support of an RDBMS. Additionally, in some dialects, it's not possible to serialise JSON arrays this way (e.g. due to bugs or limitations), so jOOQ generates arrays of objects with dummy key names, such as [{"v1":1,"v2":2}]. Also not documented.

See this article on the sobering parts of trying to standardise on SQL/JSON support across RDBMS for some background.