{"record":{"id":"ca3bb825792a31ca","repo":"hibernate/hibernate-orm","slug":"can-t-emulate-json-objectagg-with-unique-keys-cl-ca3bb8","errorCode":null,"errorMessage":"Can't emulate json_objectagg 'with unique keys' clause.","messagePattern":"Can't emulate json_objectagg 'with unique keys' clause\\.","errorType":"exception","errorClass":"QueryException","httpStatus":null,"severity":"error","filePath":"hibernate-core/src/main/java/org/hibernate/dialect/function/json/DB2JsonObjectAggFunction.java","lineNumber":37,"sourceCode":"/**\n * DB2 json_objectagg function.\n */\npublic class DB2JsonObjectAggFunction extends JsonObjectAggFunction {\n\n\tpublic DB2JsonObjectAggFunction(TypeConfiguration typeConfiguration) {\n\t\tsuper( \",\", false, typeConfiguration );\n\t}\n\n\t@Override\n\tprotected void render(\n\t\t\tSqlAppender sqlAppender,\n\t\t\tJsonObjectAggArguments arguments,\n\t\t\tPredicate filter,\n\t\t\tReturnableType<?> returnType,\n\t\t\tSqlAstTranslator<?> translator) {\n\t\tfinal boolean caseWrapper = filter != null;\n\t\tif ( arguments.uniqueKeysBehavior() == JsonObjectAggUniqueKeysBehavior.WITH ) {\n\t\t\tthrow new QueryException( \"Can't emulate json_objectagg 'with unique keys' clause.\" );\n\t\t}\n\t\tsqlAppender.appendSql( \"'{'||listagg(\" );\n\t\trenderArgument( sqlAppender, arguments.key(), arguments.nullBehavior(), translator );\n\t\tsqlAppender.appendSql( \"||':'||\" );\n\t\tif ( caseWrapper ) {\n\t\t\tif ( arguments.nullBehavior() != JsonNullBehavior.ABSENT ) {\n\t\t\t\tthrow new QueryException( \"Can't emulate json_objectagg 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, arguments.value(), arguments.nullBehavior(), translator );\n\t\t\tsqlAppender.appendSql( \" else null end)\" );\n\t\t}\n\t\telse {\n\t\t\trenderArgument( sqlAppender, arguments.value(), arguments.nullBehavior(), translator );","sourceCodeStart":19,"sourceCodeEnd":55,"githubUrl":"https://github.com/hibernate/hibernate-orm/blob/fad1729dce015f908198d57a8d80274a30f905a5/hibernate-core/src/main/java/org/hibernate/dialect/function/json/DB2JsonObjectAggFunction.java#L19-L55","documentation":"DB2's json_objectagg() is emulated by string-aggregating key||':'||value pairs with listagg(). The SQL:2016 WITH UNIQUE KEYS clause requires the database to reject duplicate keys, and the listagg-based emulation has no way to enforce that, so the translator throws as soon as it renders a query plan containing the clause.","triggerScenarios":"HQL on the DB2 dialect: select json_objectagg(key i.sku value i.name with unique keys) from OrderItem i. Any WITH UNIQUE KEYS clause (JsonObjectAggUniqueKeysBehavior.WITH) triggers the exception; WITHOUT UNIQUE KEYS or omitting the clause is accepted.","commonSituations":"Group-level aggregations where the key derives from row data and duplicates are possible; queries ported from Oracle 21c+ or SQL Server where unique-key enforcement is honored natively.","solutions":["Drop the with unique keys clause","Guarantee uniqueness upstream: aggregate over a query that already deduplicates keys (distinct, group by), and validate duplicates in application code","Use a native DB2 query if duplicate keys must be detected at query time"],"exampleFix":"// before - throws on DB2\nselect json_objectagg(key i.sku value i.name with unique keys) from OrderItem i\n\n// after - no unique-keys enforcement; deduplicate inputs yourself\nselect json_objectagg(key i.sku value i.name) from OrderItem i","handlingStrategy":"validation","validationCode":"// Reject WITH UNIQUE KEYS for json_objectagg on DB2 before execution\nstatic void assertTranslatable(SessionFactory sf, String hql) {\n    if (sf.getJdbcServices().getDialect() instanceof org.hibernate.dialect.DB2Dialect\n            && hql.toLowerCase().contains(\"with unique keys\")) {\n        throw new IllegalArgumentException(\n            \"DB2 emulation cannot enforce 'with unique keys'; deduplicate keys upstream\");\n    }\n}","typeGuard":null,"tryCatchPattern":"try {\n    return session.createQuery(hql, String.class).getSingleResult();\n} catch (org.hibernate.QueryException e) {\n    if (e.getMessage() != null && e.getMessage().contains(\"'with unique keys' clause\")) {\n        // Retry without enforcement; duplicates must be prevented by the data\n        return session.createQuery(hql.replace(\"with unique keys\", \"\"), String.class).getSingleResult();\n    }\n    throw e;\n}","preventionTips":["Deduplicate keys in a subquery (group by / distinct) instead of relying on WITH UNIQUE KEYS","Validate key uniqueness in application code when aggregating user-generated data","Treat WITH UNIQUE KEYS as an assertion, not a constraint, when targeting DB2"],"tags":["hibernate","db2","json","hql","json-objectagg","unique-keys","sql-dialect"],"backgroundTag":"json-unique-keys-unsupported","analyzedSha":"fad1729dce015f908198d57a8d80274a30f905a5","analyzedAt":"2026-08-22T04:13:57.527Z","schemaVersion":2},"datasetVersion":"2026-08-22T09:17:25.309Z"}