MULTISET值构造函数
适用于 ✅ 开源版 ✅ 专业版 ✅ 企业版
MULTISET 值构造器是 jOOQ 和标准 SQL 最强大的特性之一。它允许将非标量子查询 的结果收集到具有 MULTISET 语义的单个嵌套集合值中（序数未在元素上定义，但 jOOQ 尝试在投影 MULTISET 时保持 ORDER BY 产生的顺序）。
例如，让我们查找
- 所有作者。
- 该作者的书籍出版的语言。
- 该作者的书籍可用的书店。
这可以在单个查询中完成
SELECT
AUTHOR.FIRST_NAME,
AUTHOR.LAST_NAME,
MULTISET(
SELECT DISTINCT
LANGUAGE.CD
LANGUAGE.DESCRIPTION
FROM BOOK
JOIN LANGUAGE ON BOOK.LANGUAGE_ID = LANGUAGE.ID
WHERE BOOK.AUTHOR_ID = AUTHOR.ID
) AS BOOKS,
MULTISET(
SELECT DISTINCT BOOK_TO_BOOK_STORE.BOOK_STORE_NAME
FROM BOOK_TO_BOOK_STORE
JOIN BOOK ON BOOK_TO_BOOK_STORE.BOOK_ID = BOOK.ID
WHERE BOOK.AUTHOR_ID = AUTHOR.ID
) AS BOOK_STORES
FROM AUTHOR
ORDER BY AUTHOR.ID
var result = create.select(
AUTHOR.FIRST_NAME,
AUTHOR.LAST_NAME,
multiset(
selectDistinct(
BOOK.language().CD,
BOOK.language().DESCRIPTION)
.from(BOOK)
.where(BOOK.AUTHOR_ID.eq(AUTHOR.ID))
).as("books"),
multiset(
selectDistinct(BOOK_TO_BOOK_STORE.BOOK_STORE_NAME)
.from(BOOK_TO_BOOK_STORE)
.where(BOOK_TO_BOOK_STORE.tBook().AUTHOR_ID.eq(AUTHOR.ID))
).as("book_stores"))
.from(AUTHOR)
.orderBy(AUTHOR.ID)
.fetch();
请注意 Java 10 的 var 关键字在这里是如何发挥作用的。通常不希望表示 jOOQ 中嵌套记录或集合产生的类型。上面的 var result 推断为
Result<Record4<
String, // AUTHOR.FIRST_NAME
String, // AUTHOR.LAST_NAME
Result<Record2<
String, // LANGUAGE.CD
String // LANGUAGE.DESCRIPTION
>>, // books
Result<Record1<
String // BOOK_TO_BOOK_STORE.BOOK_STORE_NAME
>> // book_stores
>> result = ...
另请注意，在很多情况下，使用RecordMapper 在将集合嵌套到 DTO 树时非常有用，特别是与临时转换器结合使用时。
以上查询的结果可能如下所示
+----------+---------+-----------------------------+--------------------------------------------------+
|first_name|last_name|books |book_stores |
+----------+---------+-----------------------------+--------------------------------------------------+
|George |Orwell |[(en, English)] |[(Ex Libris), (Orell Füssli)] |
|Paulo |Coelho |[(de, Deutsch), (pt, {null})]|[(Buchhandlung im Volkshaus), (Ex Libris), (Ore...|
+----------+---------+-----------------------------+--------------------------------------------------+
或者，当导出为 JSON 时（或者，直接使用JSON_ARRAYAGG）
[
{
"first_name": "George",
"last_name": "Orwell",
"books": [
{
"cd": "en",
"description": "English"
}
],
"book_stores": [
{ "book_store_name": "Ex Libris" },
{ "book_store_name": "Orell Füssli" }
]
},
{
"first_name": "Paulo",
"last_name": "Coelho",
"books": [
{
"cd": "de",
"description": "Deutsch"
},
{
"cd": "pt",
"description": null
}
],
"book_stores": [
{ "book_store_name": "Buchhandlung im Volkshaus" },
{ "book_store_name": "Ex Libris" },
{ "book_store_name": "Orell Füssli" }
]
}
]
<result>
<record>
<first_name>George</first_name>
<last_name>Orwell</last_name>
<books>
<result>
<record>
<cd>en</cd>
<description>English</description>
</record>
</result>
</books>
<book_stores>
<result>
<record>
<book_store_name>Ex Libris</book_store_name>
</record>
<record>
<book_store_name>Orell Füssli</book_store_name>
</record>
</result>
</book_stores>
</record>
<record>
<first_name>Paulo</first_name>
<last_name>Coelho</last_name>
<books>
<result>
<record>
<cd>de</cd>
<description>Deutsch</description>
</record>
<record>
<cd>pt</cd>
<description/>
</record>
</result>
</books>
<book_stores>
<result>
<record>
<book_store_name>Buchhandlung im Volkshaus</book_store_name>
</record>
<record>
<book_store_name>Ex Libris</book_store_name>
</record>
<record>
<book_store_name>Orell Füssli</book_store_name>
</record>
</result>
</book_stores>
</record>
</result>
实现
坏消息是，几乎没有任何方言原生支持 MULTISET 构造器（例如，Informix 或 Oracle 支持）。在所有其他方言中，必须使用 SQL/JSON 或 SQL/XML 模拟它。以上查询在 PostgreSQL 中可能如下所示
SELECT
AUTHOR.FIRST_NAME,
AUTHOR.LAST_NAME,
(
SELECT COALESCE(
JSONB_AGG(JSONB_BUILD_ARRAY(V0, V1)),
JSONB_BUILD_ARRAY()
)
FROM (
SELECT DISTINCT
ALIAS_86077489.CD AS V0,
ALIAS_86077489.DESCRIPTION AS V1
FROM BOOK
JOIN LANGUAGE AS ALIAS_86077489
ON BOOK.LANGUAGE_ID = ALIAS_86077489.ID
WHERE BOOK.AUTHOR_ID = AUTHOR.ID
) AS T
) AS BOOKS,
(
SELECT COALESCE(
JSONB_AGG(JSONB_BUILD_ARRAY(V0)),
JSONB_BUILD_ARRAY()
)
FROM (
SELECT DISTINCT BOOK_TO_BOOK_STORE.BOOK_STORE_NAME AS V0
FROM BOOK_TO_BOOK_STORE
JOIN BOOK AS ALIAS_129518614
ON BOOK_TO_BOOK_STORE.BOOK_ID = ALIAS_129518614.ID
WHERE ALIAS_129518614.AUTHOR_ID = AUTHOR.ID
) AS T
) AS BOOK_STORES
FROM AUTHOR
ORDER BY AUTHOR.ID
您可能会注意到，这会产生与手动创建略有不同的 JSON 结构。它生成数组的数组，在格式化的结果中看起来像这样
+----------+---------+---------------------------------+----------------------------------------------------------------+ |first_name|last_name|books |book_stores | +----------+---------+---------------------------------+----------------------------------------------------------------+ |George |Orwell |[["en", "English"]] |[["Ex Libris"], ["Orell Füssli"]] | |Paulo |Coelho |[["de", "Deutsch"], ["pt", null]]|[["Buchhandlung im Volkshaus"], ["Ex Libris"], ["Orell Füssli"]]| +----------+---------+---------------------------------+----------------------------------------------------------------+
好处是
- 数组比 JSON 中的对象占用更少的空间，因此序列化格式更优化
- 数组不关心重复的列名，这可能会导致各种 JSON 解析器出现问题（即使 JSON 支持它）
- 数组元素具有明确定义的顺序，对象键没有，并且索引查找比名称查找更快
生成的 JSON 或 XML 文档将被解析并映射到 jOOQ org.jooq.Result 和 org.jooq.Record 层次结构。
默认情况下，使用“最佳”序列化格式（JSON、XML 或将来的 ARRAY），但您可以使用Settings.emulateMultiset覆盖它，它提供以下值
-
DEFAULT：让 jOOQ 决定如何序列化嵌套集合 -
XML：使用 XML 序列化嵌套集合 -
JSON：使用 JSON 序列化嵌套集合 -
JSONB：使用 JSONB 序列化嵌套集合 -
NATIVE：生成原生语法
方言支持
此示例使用 jOOQ
multiset(select(BOOK.ID, BOOK.TITLE).from(BOOK))
翻译成以下特定方言的表达式
Aurora Postgres, CockroachDB, Postgres, YugabyteDB
(
SELECT coalesce(
jsonb_agg(jsonb_build_array(v0, v1)),
jsonb_build_array()
)
FROM (
SELECT
BOOK.ID v0,
BOOK.TITLE v1
FROM BOOK
) t
)
BigQuery, Informix
MULTISET( SELECT BOOK.ID, BOOK.TITLE FROM BOOK )
DB2
(
SELECT xmlelement(
NAME result,
xmlagg(xmlelement(
NAME record,
xmlelement(NAME v0, BOOK.ID),
xmlelement(
NAME v1,
xmlattributes(
CASE
WHEN BOOK.TITLE IS NULL THEN 'true'
END AS "xsi:nil"
),
BOOK.TITLE
)
))
)
FROM BOOK
)
DuckDB
ARRAY(
SELECT t
FROM (
SELECT BOOK.ID, BOOK.TITLE
FROM BOOK
) t
)
H2
(
SELECT coalesce(
json_arrayagg(json_array(BOOK.ID, BOOK.TITLE NULL ON NULL)),
json_array(NULL ON NULL)
)
FROM BOOK
)
MariaDB
(
SELECT coalesce(
json_merge_preserve(
'[]',
concat(
'[',
group_concat(json_array(BOOK.ID, BOOK.TITLE) SEPARATOR ','),
']'
)
),
json_array()
)
FROM BOOK
)
MySQL
(
SELECT coalesce(
json_merge_preserve(
'[]',
concat(
'[',
group_concat(json_array(t.v0, t.v1) SEPARATOR ','),
']'
)
),
json_array()
)
FROM (
SELECT
BOOK.ID v0,
BOOK.TITLE v1
FROM BOOK
) t
)
Oracle
(
SELECT coalesce(
json_arrayagg(json_array(t.v0, t.v1 NULL ON NULL RETURNING clob) FORMAT JSON RETURNING clob),
json_array(RETURNING clob)
)
FROM (
SELECT
BOOK.ID v0,
BOOK.TITLE v1
FROM BOOK
) t
)
Snowflake
(
SELECT coalesce(
array_agg(array_construct(coalesce(
to_variant(BOOK.ID),
parse_json('null')
), coalesce(
to_variant(BOOK.TITLE),
parse_json('null')
))) WITHIN GROUP (ORDER BY NULL),
array_construct()
)
FROM BOOK
)
SQLite
(
SELECT coalesce(
json_group_array(json_array(t.v0, t.v1)),
json_array()
)
FROM (
SELECT
BOOK.ID v0,
BOOK.TITLE v1
FROM BOOK
) t
)
SQLServer
coalesce(
(
SELECT
v0 v0,
v1 v1
FROM (
SELECT BOOK.ID, BOOK.TITLE
FROM BOOK
) t (v0, v1)
FOR XML RAW ('record'), ELEMENTS XSINIL, BINARY BASE64, TYPE, ROOT ('result')
),
'<result/>'
)
Teradata
(
SELECT xmlelement(
NAME "result",
xmlagg(xmlelement(
NAME record,
xmlelement(NAME v0, t.v0),
xmlelement(
NAME v1,
xmlattributes(
CASE
WHEN t.v1 IS NULL THEN 'true'
END AS nil
),
t.v1
)
))
)
FROM (
SELECT
BOOK.ID v0,
BOOK.TITLE v1
FROM BOOK
) t
)
Trino
(
SELECT coalesce(
cast(array_agg(CAST(ARRAY[
CAST(t.v0 AS json),
CAST(t.v1 AS json)
] AS json)) AS json),
CAST(ARRAY[] AS json)
)
FROM (
SELECT
BOOK.ID v0,
BOOK.TITLE v1
FROM BOOK
) t
)
ASE, Access, Aurora MySQL, ClickHouse, Databricks, Derby, Exasol, Firebird, HSQLDB, Hana, MemSQL, Redshift, SQLDataWarehouse, Sybase, Vertica
/* UNSUPPORTED */
使用 jOOQ 3.21 生成。早期 jOOQ 版本的支持可能有所不同。 在我们的网站上翻译您自己的 SQL
反馈
您对此页面有任何反馈吗？ 我们很乐意听取您的意见！