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

SingleStore has no native json_arrayagg, so the dialect emulates a FILTER (WHERE ...) clause by wrapping the argument in CASE WHEN <filter> THEN arg ELSE NULL END. That wrapper can only reproduce ABSENT ON NULL semantics; with NULL ON NULL the filtered-out rows would have to contribute JSON nulls, which the CASE trick cannot express, so render() throws QueryException.

Source

Thrown at hibernate-community-dialects/src/main/java/org/hibernate/community/dialect/function/json/SingleStoreJsonArrayAggFunction.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 distinctArg) {
			sqlAppender.appendSql( "distinct " );
			arg = distinctArg.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. Drop `null on null` and rely on the default ABSENT ON NULL when using filter
2. Remove the filter clause and pre-filter rows with a WHERE clause, then use null on null
3. Aggregate the JSON array in Java instead
4. Fall back to a native SingleStore query with group_concat emulation

Example fix

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

// after: pre-filter with WHERE, keep null on null
select json_arrayagg(e.tag null on null) from E e where e.active = true

// On SingleStore, filter() may combine only with the default ABSENT ON NULL
static boolean arrayAggEmulatable(boolean filter, boolean nullOnNull, Dialect d) {
    return !(d instanceof SingleStoreDialect) || !(filter && nullOnNull);
}

try {
    return em.createQuery(hql).getResultList(); // json_arrayagg ... null on null filter ...
} catch (QueryException e) {
    if (e.getMessage() != null && e.getMessage().contains("json_arrayagg filter")) {
        // drop 'null on null' or move the predicate into WHERE and retry
    }
    throw e;
}

When it happens

Trigger: HQL `json_arrayagg(e.tag null on null) filter (where e.active = true)` (or Criteria filter plus JsonNullBehavior.NULL) on SingleStoreDialect. filter with the default ABSENT ON NULL is fine, and `null on null` without filter is fine.

Common situations: Porting SQL/JSON queries from Oracle/PostgreSQL/MySQL where json_arrayagg with null on null plus filter works; generic aggregation code that always spells out null behavior.

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/15ba2df1ee14dc44. Report an issue: GitHub.