{"record":{"id":"239cdc6b42fe62c0","repo":"hibernate/hibernate-orm","slug":"can-t-emulate-json-arrayagg-filter-clause-when-usi-239cdc","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/DB2JsonArrayAggFunction.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\tsqlAppender.appendSql( \",',')\" );\n\t\tif ( withinGroup != null && !withinGroup.isEmpty() ) {\n\t\t\ttranslator.getCurrentClauseStack().push( Clause.WITHIN_GROUP );\n\t\t\tsqlAppender.appendSql( \" within group (order by \" );\n\t\t\twithinGroup.get( 0 ).accept( translator );\n\t\t\tfor ( int i = 1; i < withinGroup.size(); i++ ) {","sourceCodeStart":41,"sourceCodeEnd":77,"githubUrl":"https://github.com/hibernate/hibernate-orm/blob/fad1729dce015f908198d57a8d80274a30f905a5/hibernate-core/src/main/java/org/hibernate/dialect/function/json/DB2JsonArrayAggFunction.java#L41-L77","documentation":"DB2 has no native json_arrayagg(), so Hibernate emulates it with listagg() over string concatenation. The aggregate FILTER clause is emulated by wrapping the argument in case when <filter> then arg else null end, which only composes with ABSENT ON NULL semantics; combined with NULL ON NULL the wrapper cannot represent 'keep nulls in the array', and the translation is rejected during SQL rendering.","triggerScenarios":"HQL on the DB2 dialect that combines the aggregate FILTER clause with the null clause: select json_arrayagg(i.price null on null) filter (where i.active = true) from OrderItem i. The check nullBehavior != JsonNullBehavior.ABSENT inside the caseWrapper branch of DB2JsonArrayAggFunction throws.","commonSituations":"Per-group JSON array reports that exclude rows with FILTER, ported from PostgreSQL or Oracle where the combination is supported; enabling the DB2 test profile in a multi-database CI matrix after the feature worked elsewhere.","solutions":["Use absent on null so the CASE wrapper composes with FILTER: json_arrayagg(i.price absent on null) filter (where i.active = true)","Push the filtering into the query itself (WHERE clause, join, or subquery) instead of FILTER, and keep null on null","Assemble the array in application code after fetching the filtered rows","Use a native DB2 query if exact NULL ON NULL + FILTER semantics are a hard requirement"],"exampleFix":"// before - throws on DB2\nselect json_arrayagg(i.price null on null) filter (where i.active = true) from OrderItem i\n\n// after - ABSENT ON NULL composes with the FILTER emulation\nselect json_arrayagg(i.price absent on null) filter (where i.active = true) from OrderItem i","handlingStrategy":"validation","validationCode":"// Reject NULL ON NULL + FILTER combinations for json_arrayagg on DB2 before execution\nstatic void assertTranslatable(SessionFactory sf, String hql) {\n    if (sf.getJdbcServices().getDialect() instanceof org.hibernate.dialect.DB2Dialect) {\n        String h = hql.toLowerCase();\n        if (h.contains(\"json_arrayagg\") && h.contains(\"null on null\") && h.contains(\"filter\")) {\n            throw new IllegalArgumentException(\n                \"DB2 cannot emulate FILTER with 'null on null'; use 'absent on null' or a WHERE filter\");\n        }\n    }\n}","typeGuard":null,"tryCatchPattern":"try {\n    return session.createQuery(hql, Object.class).getResultList();\n} catch (org.hibernate.QueryException e) {\n    if (e.getMessage() != null && e.getMessage().contains(\"json_arrayagg filter clause\")) {\n        // Retry with absent-on-null semantics; nulls are dropped but the array still renders\n        return session.createQuery(hql.replace(\"null on null\", \"absent on null\"), Object.class).getResultList();\n    }\n    throw e;\n}","preventionTips":["Prefer 'absent on null' whenever an aggregate JSON function also uses FILTER","Model multi-database support explicitly: keep a per-dialect query variant for aggregate JSON usage","Include a DB2 profile in CI when production may ever target DB2"],"tags":["hibernate","db2","json","hql","json-arrayagg","aggregate-filter","sql-dialect"],"backgroundTag":"aggregate-filter-null-behavior-conflict","analyzedSha":"fad1729dce015f908198d57a8d80274a30f905a5","analyzedAt":"2026-08-22T04:13:57.527Z","schemaVersion":2},"datasetVersion":"2026-08-22T09:17:25.309Z"}