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

MariaDB json_arrayagg is emulated in Hibernate. When a FILTER clause is present, the emulation wraps the argument in CASE WHEN filter THEN arg ELSE NULL END. This wrapper cannot reproduce 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/MariaDBJsonArrayAggFunction.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 );
		}
		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

1. Remove NULL ON NULL so ABSENT ON NULL applies.
2. Remove the FILTER clause and filter rows in the WHERE clause or a subquery.
3. Pre-filter with a subquery, then aggregate without the FILTER clause.
4. Use a native MariaDB query when both clauses are mandatory.

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

boolean mariadb = session.getJdbcServices().getDialect() instanceof org.hibernate.dialect.MariaDBDialect;
if (mariadb && hasFilterClause && nullOnNull) {
    throw new UnsupportedOperationException("MariaDB: json_arrayagg cannot combine FILTER with NULL ON NULL");
}

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;
}

When it happens

Trigger: An HQL json_arrayagg call on MariaDB combines FILTER(WHERE ...) with NULL ON NULL. The nullBehavior != JsonNullBehavior.ABSENT check inside the caseWrapper branch fires.

Common situations: Queries written for MySQL 8 (same JSON functions, native FILTER support) moved to MariaDB. Reports that need filtered aggregates and explicit JSON nulls.

Related errors

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

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