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

This is the generic json_arrayagg emulation used by dialects without native support. When a FILTER clause is present, the value is wrapped in CASE WHEN filter THEN arg ELSE NULL END. That wrapper cannot express NULL ON NULL, so a null behavior other than ABSENT combined with a filter throws this QueryException.

Source

Thrown at hibernate-core/src/main/java/org/hibernate/dialect/function/json/JsonArrayAggFunction.java:85

		final SqlAstNode firstArg = sqlAstArguments.get( 0 );
		final JsonNullBehavior nullBehavior;
		if ( sqlAstArguments.size() > 1 ) {
			nullBehavior = (JsonNullBehavior) sqlAstArguments.get( 1 );
		}
		else {
			nullBehavior = JsonNullBehavior.ABSENT;
		}
		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 );
		}
		if ( withinGroup != null && !withinGroup.isEmpty() ) {
			translator.getCurrentClauseStack().push( Clause.WITHIN_GROUP );
			sqlAppender.appendSql( " order by " );
			withinGroup.get( 0 ).accept( translator );
			for ( int i = 1; i < withinGroup.size(); i++ ) {
				sqlAppender.appendSql( ',' );

View on GitHub (pinned to fad1729dce)

Solutions

Remove NULL ON NULL so the default ABSENT ON NULL applies.
Remove the FILTER clause and move the predicate into the WHERE clause when the semantics permit.
Pre-filter rows in a subquery, then aggregate without the FILTER clause.
Run the statement on a dialect with native json_arrayagg when both clauses are required.

Example fix

// before
select json_arrayagg(e.val null on null filter (where e.active = true)) from Entity e

// after
select json_arrayagg(e.val absent on null) from Entity e where e.active = true

Defensive patterns

Strategy: try-catch

Validate before calling

// Only combine FILTER with NULL ON NULL when the dialect supports it natively.
Dialect d = session.getJdbcServices().getDialect();
boolean nativeFilter = d instanceof org.hibernate.dialect.PostgreSQLDialect;
if (!nativeFilter && hasFilterClause && nullOnNull) {
    throw new UnsupportedOperationException("json_arrayagg FILTER plus NULL ON NULL is not emulatable on " + d);
}

Try / catch

try {
    return session.createQuery(hql).getSingleResult();
} catch (org.hibernate.QueryException e) {
    if (e.getMessage() != null && e.getMessage().contains("json_arrayagg filter")) {
        return session.createQuery(hqlAbsentNullWithWhere).getSingleResult();
    }
    throw e;
}

Prevention

Default to ABSENT ON NULL in HQL so queries stay portable across dialects.
Move filter predicates into WHERE when the aggregate reads the whole group anyway.
Add a CI profile for each supported database.

When it happens

Trigger: An HQL json_arrayagg call uses both a FILTER clause and NULL ON NULL while running on a dialect that relies on this generic emulation (for example H2 or Derby class dialects).

Common situations: A query written for a database with native json_arrayagg runs in H2-based integration tests. Teams add NULL ON NULL to keep nulls visible in arrays and later add filters.

Related errors

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