{"record":{"id":"90204d8794be7337","repo":"hibernate/hibernate-orm","slug":"can-t-emulate-json-arrayagg-filter-clause-when-usi-90204d","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/JsonArrayAggFunction.java","lineNumber":85,"sourceCode":"\t\tfinal SqlAstNode firstArg = sqlAstArguments.get( 0 );\n\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 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":67,"sourceCodeEnd":103,"githubUrl":"https://github.com/hibernate/hibernate-orm/blob/fad1729dce015f908198d57a8d80274a30f905a5/hibernate-core/src/main/java/org/hibernate/dialect/function/json/JsonArrayAggFunction.java#L67-L103","documentation":"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.","triggerScenarios":"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).","commonSituations":"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.","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."],"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":"// Only combine FILTER with NULL ON NULL when the dialect supports it natively.\nDialect d = session.getJdbcServices().getDialect();\nboolean nativeFilter = d instanceof org.hibernate.dialect.PostgreSQLDialect;\nif (!nativeFilter && hasFilterClause && nullOnNull) {\n    throw new UnsupportedOperationException(\"json_arrayagg FILTER plus NULL ON NULL is not emulatable on \" + d);\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":["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."],"tags":["hibernate","json","json-arrayagg","filter-clause","null-behavior","dialect-emulation"],"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"}