使用 jOOQ 创建自定义聚合函数

Question

语境我正在针对PostgreSQL数据库使用jOOQ。 我想在 a 的结果集上使用。jsonb_object_agg(name, value)LEFT OUTER JOIN问题连接是OUTER一个，有时name聚合函数的组件很简单null：那行不通。然后我会去：COALESCE(    json_object_agg(table.name, table.value) FILTER (WHERE table.name IS NOT NULL),    '{}')::json到目前为止，我用来调用的代码jsonb_object_agg（不完全是，但归结为）如下：public static Field<?> jsonbObjectAgg(final Field<?> key, final Select<?> select) {    return DSL.field("jsonb_object_agg({0}, ({1}))::jsonb", JSON_TYPE, key, select);}...在哪里JSON_TYPE：private static final DataType<JsonNode> JSON_TYPE = SQLDataType.VARCHAR.asConvertedDataType(/* a custom Converter */);不完整的解决方案我很想利用jOOQ的AggregateFilterStep界面，特别是能够使用它的AggregateFilterStep#filterWhere(Condition... conditions).但是，（间接通过和）的org.jooq.impl.Function类对其 的可见性受到限制，因此我不能盲目地回收 的实现：implements AggregateFilterStepAgregateFunctionArrayAggOrderBySteppackageDSL#ArrayAggOrderBySteppublic static <T> ArrayAggOrderByStep<T[]> arrayAgg(Field<T> field) {    return new org.jooq.impl.Function<T[]>(Term.ARRAY_AGG, field.getDataType().getArrayDataType(), nullSafe(field));}尝试我最接近合理的东西是......构建我自己的coalesceAggregation函数，专门合并聚合字段：//                                  Can't quite use AggregateFunction there//                                                   v   vpublic static <T> Field<T> coalesceAggregation(final Field<T> agg, final Condition coalesceWhen, @NonNull final T coalesceTo) {    return DSL.coalesce(DSL.field("{0} FILTER (WHERE {1})", agg.getType(), agg, coalesceWhen), coalesceTo);}public static <T> Field<T> coalesceAggregation(final Field<T> agg, @NonNull final T coalesceTo) {    return coalesceAggregation(agg, agg.isNotNull(), coalesceTo);}......但是我再跑进我的问题T类型是JsonNode，其中DSL#coalesce好像CAST我coalesceTo来varchar。或者，你知道：DSL.field("COALESCE(jsonb_object_agg({0}, ({1})) FILTER (WHERE {0} IS NOT NULL), '{}')::jsonb", JSON_TYPE, key, select)但这将是最后的手段：感觉就像我离让用户将他们想要的任何 SQL 注入我的数据库只差一步了🙄简而言之jOOQ 中是否有一种方法可以“正确”实现自己的聚合函数，作为实际的org.jooq.AgregateFunction？我想尽可能避免生成它jooq-codegen（并不是我不喜欢它——这只是我们的管道太糟糕了）

Answer 1

从 jOOQ 3.14.0 开始

该JSON_OBJECTAGG聚合函数是在本机现在jOOQ支持：

DSL.jsonObjectAgg(TABLE.NAME, TABLE.VALUE).filterWhere(TABLE.NAME.isNotNull());

FILTER在 jOOQ 3.14.8 中添加了对子句的支持。

从 jOOQ 3.14.8 和 3.15.0 开始

如果 jOOQ 没有实现特定的聚合函数，您现在可以指定DSL.aggregate()使用自定义聚合函数。

DSL.aggregate("json_object_agg", SQLDataType.JSON, TABLE.NAME, TABLE.VALUE)
   .filterWhere(TABLE.NAME.isNotNull());

这是通过https://github.com/jOOQ/jOOQ/issues/1729实现的

对于 jOOQ 3.14.0

jOOQ DSLAPI 中缺少一个功能，即创建纯 SQL 聚合函数。这还不可用的原因（从 jOOQ 3.11 开始）是因为有很多微妙的内部结构来指定支持所有供应商特定选项的供应商不可知聚合函数，包括：

• FILTER (WHERE ...) 条款（如您在问题中提到的），必须使用 CASE

• OVER (...) 子句将聚合函数转换为窗口函数

• WITHIN GROUP (ORDER BY ...) 支持有序集合聚合函数的子句

• DISTINCT 条款，如果支持

• 其他特定于供应商的聚合函数扩展

在您的特定情况下，简单的解决方法是一直使用纯 SQL 模板，正如您在问题中提到的：

DSL.field("COALESCE(jsonb_object_agg({0}, ({1})) FILTER (WHERE {0} IS NOT NULL), '{}')::jsonb", JSON_TYPE, key, select)

或者你做你之前提到的事情。关于这种担忧：

...但我随后遇到了我的 T 类型为 JsonNode 的问题，其中 DSL#coalesce 似乎将我的 coalesceTo 转换为 varchar。

那可能是因为您使用了agg.getType()which returnsClass<?>而不是agg.getDataType()which returns DataType<?>。

但这将是最后的手段：感觉就像我离让用户将他们想要的任何 SQL 注入我的数据库仅一步之遥

我不确定为什么这是一个问题。您仍然可以自己控制普通 SQL API 的使用，用户将无法注入任意内容key，select因为您也控制这些元素。