{"record":{"id":"c406e4b2ea9af089","repo":"hibernate/hibernate-orm","slug":"can-t-emulate-json-arrayagg-filter-clause-when-usi-c406e4","errorCode":null,"errorMessage":"Can't emulate json_arrayagg filter clause when using 'null on null' clause.","messagePattern":"Can't emulate json_arrayagg filter clause when using 'null on null' clause\\.","errorType":"exception","errorClass":"QueryException","httpStatus":null,"severity":"error","filePath":"hibernate-core/src/main/java/org/hibernate/dialect/function/json/MariaDBJsonArrayAggFunction.java","lineNumber":59,"sourceCode":"\t\tfinal JsonNullBehavior nullBehavior;\n\t\tif ( sqlAstArguments.size() > 1 ) {\n\t\t\tnullBehavior = (JsonNullBehavior) sqlAstArguments.get( 1 );\n\t\t}\n\t\telse {\n\t\t\tnullBehavior = JsonNullBehavior.ABSENT;\n\t\t}\n\t\tfinal SqlAstNode firstArg = sqlAstArguments.get( 0 );\n\t\tfinal Expression arg;\n\t\tif ( firstArg instanceof Distinct distinct ) {\n\t\t\tsqlAppender.appendSql( \"distinct \" );\n\t\t\targ = distinct.getExpression();\n\t\t}\n\t\telse {\n\t\t\targ = (Expression) firstArg;\n\t\t}\n\t\tif ( caseWrapper ) {\n\t\t\tif ( nullBehavior != JsonNullBehavior.ABSENT ) {\n\t\t\t\tthrow new QueryException( \"Can't emulate json_arrayagg filter clause when using 'null on null' clause.\" );\n\t\t\t}\n\t\t\ttranslator.getCurrentClauseStack().push( Clause.WHERE );\n\t\t\tsqlAppender.appendSql( \"case when \" );\n\t\t\tfilter.accept( translator );\n\t\t\ttranslator.getCurrentClauseStack().pop();\n\t\t\tsqlAppender.appendSql( \" then \" );\n\t\t\trenderArgument( sqlAppender, arg, nullBehavior, translator );\n\t\t\tsqlAppender.appendSql( \" else null end)\" );\n\t\t}\n\t\telse {\n\t\t\trenderArgument( sqlAppender, arg, nullBehavior, translator );\n\t\t}\n\t\tif ( withinGroup != null && !withinGroup.isEmpty() ) {\n\t\t\ttranslator.getCurrentClauseStack().push( Clause.WITHIN_GROUP );\n\t\t\tsqlAppender.appendSql( \" order by \" );\n\t\t\twithinGroup.get( 0 ).accept( translator );\n\t\t\tfor ( int i = 1; i < withinGroup.size(); i++ ) {\n\t\t\t\tsqlAppender.appendSql( ',' );","sourceCodeStart":41,"sourceCodeEnd":77,"githubUrl":"https://github.com/hibernate/hibernate-orm/blob/fad1729dce015f908198d57a8d80274a30f905a5/hibernate-core/src/main/java/org/hibernate/dialect/function/json/MariaDBJsonArrayAggFunction.java#L41-L77","documentation":"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.","triggerScenarios":"An HQL json_arrayagg call on MariaDB combines FILTER(WHERE ...) with NULL ON NULL. The nullBehavior != JsonNullBehavior.ABSENT check inside the caseWrapper branch fires.","commonSituations":"Queries written for MySQL 8 (same JSON functions, native FILTER support) moved to MariaDB. Reports that need filtered aggregates and explicit JSON nulls.","solutions":["Remove NULL ON NULL so ABSENT ON NULL applies.","Remove the FILTER clause and filter rows in the WHERE clause or a subquery.","Pre-filter with a subquery, then aggregate without the FILTER clause.","Use a native MariaDB query when both clauses are mandatory."],"exampleFix":"// before\nselect json_arrayagg(e.val null on null filter (where e.active = true)) from Entity e\n\n// after\nselect json_arrayagg(e.val absent on null) from Entity e where e.active = true","handlingStrategy":"try-catch","validationCode":"boolean mariadb = session.getJdbcServices().getDialect() instanceof org.hibernate.dialect.MariaDBDialect;\nif (mariadb && hasFilterClause && nullOnNull) {\n    throw new UnsupportedOperationException(\"MariaDB: json_arrayagg cannot combine FILTER with NULL ON NULL\");\n}","typeGuard":null,"tryCatchPattern":"try {\n    return session.createQuery(hql).getSingleResult();\n} catch (org.hibernate.QueryException e) {\n    if (e.getMessage() != null && e.getMessage().contains(\"json_arrayagg filter\")) {\n        return session.createQuery(hqlAbsentNullWithWhere).getSingleResult();\n    }\n    throw e;\n}","preventionTips":["Treat MariaDB and MySQL 8 as different JSON dialects; do not share clause-heavy queries blindly.","Keep FILTER-free aggregate queries as the portable baseline.","Test JSON aggregate queries on MariaDB specifically."],"tags":["hibernate","mariadb","json","json-arrayagg","filter-clause","null-behavior"],"backgroundTag":"json-aggregate-filter-null-on-null","analyzedSha":"fad1729dce015f908198d57a8d80274a30f905a5","analyzedAt":"2026-08-22T04:13:57.527Z","schemaVersion":2},"datasetVersion":"2026-08-22T09:17:25.309Z"}