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

SAP HANA has no json_arrayagg, so Hibernate emulates it with string aggregation and a CASE expression when a FILTER clause is present. The CASE wrapper can only skip rows, so it cannot reproduce NULL ON NULL semantics, where SQL nulls become JSON nulls in the array. When both a filter and a null behavior other than ABSENT are requested, this QueryException is thrown.

Source

Thrown at hibernate-core/src/main/java/org/hibernate/dialect/function/json/HANAJsonArrayAggFunction.java:60

		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( " order by " );
			withinGroup.get( 0 ).accept( translator );
			for ( int i = 1; i < withinGroup.size(); i++ ) {

View on GitHub (pinned to fad1729dce)

Solutions

Remove NULL ON NULL and rely on the default ABSENT ON NULL, which the CASE emulation supports.
Remove the FILTER clause and move the condition into the query WHERE clause if the semantics allow it.
Keep NULL ON NULL and filter with a subquery instead of the FILTER clause.
Move the aggregate to a native HANA SQL query if 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

// Guard the clause combination before you run the aggregate.
boolean hana = session.getJdbcServices().getDialect() instanceof org.hibernate.dialect.HANADialect;
if (hana && hasFilterClause && nullOnNull) {
    throw new UnsupportedOperationException("HANA: use either FILTER or NULL ON NULL, not both, on json_arrayagg");
}

Try / catch

try {
    return session.createQuery(hql).getSingleResult();
} catch (org.hibernate.QueryException e) {
    if (e.getMessage() != null && e.getMessage().contains("json_arrayagg filter")) {
        // Retry with ABSENT ON NULL and the predicate moved to WHERE.
        return session.createQuery(fallbackHql).getSingleResult();
    }
    throw e;
}

Prevention

Use ABSENT ON NULL by default; add NULL ON NULL only after you confirm the dialect supports it with filters.
Prefer WHERE predicates over FILTER clauses for portable HQL.
Run aggregate JSON queries against the production dialect in CI.

When it happens

Trigger: An HQL json_arrayagg call combines a FILTER clause, for example FILTER(WHERE x > 0), with NULL ON NULL: json_arrayagg(val NULL ON NULL FILTER(WHERE ...)). On HANA, nullBehavior != JsonNullBehavior.ABSENT together with a filter triggers the error.

Common situations: A query written for PostgreSQL (which supports both clauses natively) runs against a HANA production database. Teams enable NULL ON NULL to preserve explicit nulls in JSON arrays and later add a FILTER clause for a report.

Related errors

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