jOOQ Multiset Operator 在查询中返回嵌套的 JsonArray。

huangapple go评论71阅读模式
英文:

jOOQ Multiset Operator returns nested JsonArray on query

问题

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][1], we tried to utilize the jOOQ Multiset Operator. 
For that, we are using the following 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.

  [1]: https://blog.jooq.org/jooq-3-15s-new-multiset-operator-will-change-how-you-think-about-sql/
英文:

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.

答案1

得分: 3

直接使用 jOOQ 执行

这篇 jOOQ 博客上的文章解释了为什么应该使用 jOOQ 来执行 jOOQ 查询,而不是使用第三方工具。其中一个原因是,类型安全的 MULTISET(和 ROW)反序列化依赖于 jOOQ 实现的内部 SQL/JSON 序列化格式的知识。jOOQ 可以在 JDBC(阻塞)或 R2DBC(非阻塞)上直接支持这种反序列化。

使用内部 API 将 JSON 转换为 Result

你可以使用 jOOQ 的内部 API 将 JSON 数据结构转换为 org.jooq.Result,然后可以用它来转换嵌套的记录结构到你自己的 MyPojo 类型。由于这涉及内部 API,我无法在 Stack Overflow 上详细解释它的工作原理,因为答案可能在一个月内就过时了。

但你可以让它工作,甚至在你可能正在使用的第三方 vert.x-to-jOOQ 桥接中实现它。

无论如何,有一个待处理的功能请求,以将此功能作为公共 API 提供:#12012,因此将来的第三方工具也可以提供此功能。

为什么是数组的数组?

使用数组的数组而不是对象的数组作为首选序列化格式有各种原因,包括:

  • 它不太冗长,因此数据传输量更少(每行的冗余!)
  • 通过索引访问列比通过字符串访问列更快
  • 列名可能会引起歧义(例如,SELECT 1 AS a, 2 AS a, t.a FROM t),因此不可靠

请注意,确切的序列化格式未记录,因此在不同版本之间可能会更改,例如,当 RDBMS 的 SQL/JSON 支持存在限制时。此外,在某些方言中,以这种方式无法序列化 JSON 数组(例如,由于错误或限制),因此 jOOQ 生成带有虚拟键名的对象数组,如 [{"v1":1,"v2":2}]。这也未记录。

阅读此文章,了解尝试在不同 RDBMS 上标准化 SQL/JSON 支持时的一些警示部分

英文:

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.

答案2

得分: 0

从我的角度来看,将其集成到流式框架中(例如Akka StreamsSmallRye Mutiny [在Quarkus中使用],Vert.x)是必不可少的。

所使用的类型(例如SmallRye Mutiny中的Uni和Multi)与JDK标准类型(例如CompletionStage)不同。转换是可能的,但并非总是有用的。多行的流可以转换为包含所有元素列表的完成阶段。对于CompletionStage,有一个转换器 可用于创建SmallRye Multi。这可以实现,但流则变得无用,因为结果已经可用。因此,流必须直接填充(无需进行此类转换)以利用流的优势(例如惰性评估和降低内存消耗)。

第三方库,如vertex-jooq,填补了这个空白。

英文:

From my perspective the integration into streaming frameworks (e.g. Akka Streams, SmallRye Mutiny [used in Quarkus], Vert.x) is essential.

The used types (such as Uni, Multi for SmallRye Mutiny)
are different compared to the JDK standard types such as CompletionStage.
Conversions are possible but not always useful. A stream of multiple rows can be converted to a completion stage that contains a list of all elements.
For CompletionStage is a converter available to create a SmallRye Multi.
That works, but then a stream is useless because the results are already available.
Therefore the stream must be filled directly (without such conversion) to use the advantages of a stream (such as lazy evaluation, and reducing memory consumption).

3rd party libs such as vertex-jooq fills the gap.

huangapple
  • 本文由 发表于 2023年3月9日 23:15:39
  • 转载请务必保留本文链接:https://go.coder-hub.com/75686564.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定