{"record":{"id":"f2889baa4b865ac0","repo":"hibernate/hibernate-orm","slug":"can-t-emulate-json-objectagg-with-unique-keys-cl-f2889b","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/HANAJsonObjectAggFunction.java","lineNumber":37,"sourceCode":"/**\n * SAP HANA json_objectagg function.\n */\npublic class HANAJsonObjectAggFunction extends JsonObjectAggFunction {\n\n\tpublic HANAJsonObjectAggFunction(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( \"'{'||string_agg(\" );\n\t\trenderArgument( sqlAppender, arguments.key(), JsonNullBehavior.NULL, 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/HANAJsonObjectAggFunction.java#L19-L55","documentation":"SAP HANA lacks native json_objectagg, so Hibernate emulates it with string_agg. The emulation cannot enforce key uniqueness, so a WITH UNIQUE KEYS request is rejected with this QueryException before SQL is generated.","triggerScenarios":"An HQL query on HANA calls json_objectagg(key VALUE value WITH UNIQUE KEYS). The check arguments.uniqueKeysBehavior() == JsonObjectAggUniqueKeysBehavior.WITH fires and rendering stops.","commonSituations":"Queries ported from Oracle or MySQL 8, where WITH UNIQUE KEYS is supported. Mappings that produce duplicate keys per group and rely on the database to drop duplicates.","solutions":["Remove WITH UNIQUE KEYS from the json_objectagg call on HANA.","Deduplicate key/value pairs in a subquery or in Java before the aggregate.","Use a native HANA query when unique-key enforcement must stay in the database.","Validate keys for uniqueness in application code after the aggregate returns."],"exampleFix":"// before\nselect json_objectagg(k value v with unique keys) from Entity e\n\n// after\nselect json_objectagg(k value v) from Entity e","handlingStrategy":"try-catch","validationCode":"boolean hana = session.getJdbcServices().getDialect() instanceof org.hibernate.dialect.HANADialect;\nif (hana && withUniqueKeys) {\n    throw new UnsupportedOperationException(\"HANA json_objectagg cannot enforce WITH UNIQUE KEYS; deduplicate first\");\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(\"with unique keys\")) {\n        // Retry without WITH UNIQUE KEYS after deduplicating in a subquery.\n        return session.createQuery(fallbackHql).getSingleResult();\n    }\n    throw e;\n}","preventionTips":["Do not rely on WITH UNIQUE KEYS for correctness; deduplicate keys in a subquery or in Java.","Check the HANA emulation notes before using optional json_objectagg clauses."],"tags":["hibernate","hana","json","json-objectagg","unique-keys","dialect-emulation"],"backgroundTag":"json-objectagg-unique-keys-unsupported","analyzedSha":"fad1729dce015f908198d57a8d80274a30f905a5","analyzedAt":"2026-08-22T04:13:57.527Z","schemaVersion":2},"datasetVersion":"2026-08-22T09:17:25.309Z"}