{"record":{"id":"b8138d5c46bf08b4","repo":"hibernate/hibernate-orm","slug":"can-t-emulate-json-arrayagg-filter-clause-when-usi-b8138d","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/HANAJsonArrayAggFunction.java","lineNumber":60,"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\tsqlAppender.appendSql( \",','\" );\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++ ) {","sourceCodeStart":42,"sourceCodeEnd":78,"githubUrl":"https://github.com/hibernate/hibernate-orm/blob/fad1729dce015f908198d57a8d80274a30f905a5/hibernate-core/src/main/java/org/hibernate/dialect/function/json/HANAJsonArrayAggFunction.java#L42-L78","documentation":"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.","triggerScenarios":"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.","commonSituations":"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.","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."],"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":"// Guard the clause combination before you run the aggregate.\nboolean hana = session.getJdbcServices().getDialect() instanceof org.hibernate.dialect.HANADialect;\nif (hana && hasFilterClause && nullOnNull) {\n    throw new UnsupportedOperationException(\"HANA: use either FILTER or NULL ON NULL, not both, on json_arrayagg\");\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        // Retry with ABSENT ON NULL and the predicate moved to WHERE.\n        return session.createQuery(fallbackHql).getSingleResult();\n    }\n    throw e;\n}","preventionTips":["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."],"tags":["hibernate","hana","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"}