hibernate/hibernate-orm · error · QueryException

Can't emulate json_arrayagg filter clause when using 'null o

Error message

Can't emulate json_arrayagg filter clause when using 'null on null' clause.

What it means

DB2 has no native json_arrayagg(), so Hibernate emulates it with listagg() over string concatenation. The aggregate FILTER clause is emulated by wrapping the argument in case when <filter> then arg else null end, which only composes with ABSENT ON NULL semantics; combined with NULL ON NULL the wrapper cannot represent 'keep nulls in the array', and the translation is rejected during SQL rendering.

Source

Thrown at hibernate-core/src/main/java/org/hibernate/dialect/function/json/DB2JsonArrayAggFunction.java:59

		final JsonNullBehavior nullBehavior;
		if ( sqlAstArguments.size() > 1 ) {
			nullBehavior = (JsonNullBehavior) sqlAstArguments.get( 1 );
		}
		else {
			nullBehavior = JsonNullBehavior.ABSENT;
		}
		final SqlAstNode firstArg = sqlAstArguments.get( 0 );
		final Expression arg;
		if ( firstArg instanceof Distinct distinct ) {
			sqlAppender.appendSql( "distinct " );
			arg = distinct.getExpression();
		}
		else {
			arg = (Expression) firstArg;
		}
		if ( caseWrapper ) {
			if ( nullBehavior != JsonNullBehavior.ABSENT ) {
				throw new QueryException( "Can't emulate json_arrayagg filter clause when using 'null on null' clause." );
			}
			translator.getCurrentClauseStack().push( Clause.WHERE );
			sqlAppender.appendSql( "case when " );
			filter.accept( translator );
			translator.getCurrentClauseStack().pop();
			sqlAppender.appendSql( " then " );
			renderArgument( sqlAppender, arg, nullBehavior, translator );
			sqlAppender.appendSql( " else null end)" );
		}
		else {
			renderArgument( sqlAppender, arg, nullBehavior, translator );
		}
		sqlAppender.appendSql( ",',')" );
		if ( withinGroup != null && !withinGroup.isEmpty() ) {
			translator.getCurrentClauseStack().push( Clause.WITHIN_GROUP );
			sqlAppender.appendSql( " within group (order by " );
			withinGroup.get( 0 ).accept( translator );
			for ( int i = 1; i < withinGroup.size(); i++ ) {

View on GitHub (pinned to fad1729dce)

Solutions

1. Use absent on null so the CASE wrapper composes with FILTER: json_arrayagg(i.price absent on null) filter (where i.active = true)
2. Push the filtering into the query itself (WHERE clause, join, or subquery) instead of FILTER, and keep null on null
3. Assemble the array in application code after fetching the filtered rows
4. Use a native DB2 query if exact NULL ON NULL + FILTER semantics are a hard requirement

Example fix

// before - throws on DB2
select json_arrayagg(i.price null on null) filter (where i.active = true) from OrderItem i

// after - ABSENT ON NULL composes with the FILTER emulation
select json_arrayagg(i.price absent on null) filter (where i.active = true) from OrderItem i

// Reject NULL ON NULL + FILTER combinations for json_arrayagg on DB2 before execution
static void assertTranslatable(SessionFactory sf, String hql) {
    if (sf.getJdbcServices().getDialect() instanceof org.hibernate.dialect.DB2Dialect) {
        String h = hql.toLowerCase();
        if (h.contains("json_arrayagg") && h.contains("null on null") && h.contains("filter")) {
            throw new IllegalArgumentException(
                "DB2 cannot emulate FILTER with 'null on null'; use 'absent on null' or a WHERE filter");
        }
    }
}

try {
    return session.createQuery(hql, Object.class).getResultList();
} catch (org.hibernate.QueryException e) {
    if (e.getMessage() != null && e.getMessage().contains("json_arrayagg filter clause")) {
        // Retry with absent-on-null semantics; nulls are dropped but the array still renders
        return session.createQuery(hql.replace("null on null", "absent on null"), Object.class).getResultList();
    }
    throw e;
}

When it happens

Trigger: HQL on the DB2 dialect that combines the aggregate FILTER clause with the null clause: select json_arrayagg(i.price null on null) filter (where i.active = true) from OrderItem i. The check nullBehavior != JsonNullBehavior.ABSENT inside the caseWrapper branch of DB2JsonArrayAggFunction throws.

Common situations: Per-group JSON array reports that exclude rows with FILTER, ported from PostgreSQL or Oracle where the combination is supported; enabling the DB2 test profile in a multi-database CI matrix after the feature worked elsewhere.

Related errors

• Can't emulate json_objectagg filter clause when using 'null
• Can't emulate json_objectagg 'with unique keys' clause.
• Can't emulate null on error clause on DB2
• Can't emulate on error clause on CockroachDB
• Can't emulate on empty clause on CockroachDB

AI-assisted analysis of hibernate/hibernate-orm@fad1729dce (2026-08-22). Data as JSON: /api/errors/239cdc6b42fe62c0. Report an issue: GitHub.