Create a custom Aggregate Function with jOOQ

Question

Context

I am working with jOOQ against a PostgreSQL database.
I want to use jsonb_object_agg(name, value) onto the resultset of a LEFT OUTER JOIN.

Problem

The join being an OUTER one, sometimes the name component of the aggregation function is simply null: that can't work. I would then go for:

COALESCE(
    json_object_agg(table.name, table.value) FILTER (WHERE table.name IS NOT NULL),
    '{}'
)::json

As of now, the code I use to call jsonb_object_agg is (not exactly, but boils down to) the following:

public static Field jsonbObjectAgg(final Field key, final Select select) {
    return DSL.field("jsonb_object_agg({0}, ({1}))::jsonb", JSON_TYPE, key, select);
}

... where JSON_TYPE is:

private static final DataType JSON_TYPE = SQLDataType.VARCHAR.asConvertedDataType(/* a custom Converter */);

Incomplete solution

I would love to leverage jOOQ's AggregateFilterStep interface, and in particular, to be able to use its AggregateFilterStep#filterWhere(Condition... conditions).

However, the org.jooq.impl.Function class that implements AggregateFilterStep (indirectly via AgregateFunction and ArrayAggOrderByStep) is restricted in visibility to its package, so I can't just recycle blindly the implementation of DSL#ArrayAggOrderByStep:

public static  ArrayAggOrderByStep arrayAgg(Field field) {
    return new org.jooq.impl.Function(Term.ARRAY_AGG, field.getDataType().getArrayDataType(), nullSafe(field));
}

Attempts

The closest I got to something reasonable is... building my own coalesceAggregation function that specifically coalesces aggregated fields:

//                                  Can't quite use AggregateFunction there
//                                                   v   v
public static  Field coalesceAggregation(final Field agg, final Condition coalesceWhen, @NonNull final T coalesceTo) {
    return DSL.coalesce(DSL.field("{0} FILTER (WHERE {1})", agg.getType(), agg, coalesceWhen), coalesceTo);
}

public static  Field coalesceAggregation(final Field agg, @NonNull final T coalesceTo) {
    return coalesceAggregation(agg, agg.isNotNull(), coalesceTo);
}

... But I then ran into issues with my T type being JsonNode, where DSL#coalesce seems to CAST my coalesceTo to varchar.

Or, you know:

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

But that'd be the very last resort: it'd feel like I'd merely be one step away from letting the user inject any SQL they want into my database

In short

Is there a way in jOOQ to "properly" implement one's own aggregate function, as an actual org.jooq.AgregateFunction?
I'd like to avoid having it generated by jooq-codegen as much as possible (not that I don't like it – it's just our pipeline that's horrible).

user_521799 · Answer

Starting with jOOQ 3.14.0

The JSON_OBJECTAGG aggregate function is supported natively in jOOQ now:

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

Support for the FILTER clause was added in jOOQ 3.14.8.

Starting with jOOQ 3.14.8 and 3.15.0

If jOOQ doesn't implement a specific aggregate function, you can now specify DSL.aggregate() to make use of custom aggregate functions.

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

This was implemented with https://github.com/jOOQ/jOOQ/issues/1729

Pre jOOQ 3.14.0

There's a missing feature in the jOOQ DSL API, namely to create plain SQL aggregate functions. The reason why this is not available yet (as of jOOQ 3.11) is because there are a lot of delicate internals to specifying a vendor agnostic aggregate function that supports all of the vendor-specific options including:

FILTER (WHERE ...) clause (as you mentioned in the question), which has to be emulated using CASE
OVER (...) clause to turn an aggregate function into a window function
WITHIN GROUP (ORDER BY ...) clause to support ordered set aggregate functions
DISTINCT clause, where supported
Other, vendor-specific extensions to aggregate functions

The easy workaround in your specific case is to use plain SQL templating all the way as you mentioned in your question as well:

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

Or you do the thing you've mentioned previously. Regarding that concern:

... But I then ran into issues with my T type being JsonNode, where DSL#coalesce seems to CAST my coalesceTo to varchar.

That's probably because you used agg.getType() which returns Class instead of agg.getDataType() which returns DataType.