{"record":{"id":"248eab569577f82d","repo":"hibernate/hibernate-orm","slug":"can-t-emulate-json-objectagg-with-unique-keys-cl","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-community-dialects/src/main/java/org/hibernate/community/dialect/function/json/SingleStoreJsonObjectAggFunction.java","lineNumber":37,"sourceCode":"/**\n * SingleStore json_objectagg function.\n */\npublic class SingleStoreJsonObjectAggFunction extends JsonObjectAggFunction {\n\n\tpublic SingleStoreJsonObjectAggFunction(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( \"concat('{',group_concat(concat(to_json(\" );\n\t\targuments.key().accept( 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-community-dialects/src/main/java/org/hibernate/community/dialect/function/json/SingleStoreJsonObjectAggFunction.java#L19-L55","documentation":"SingleStore's json_objectagg emulation is a group_concat over key/value pairs, which cannot enforce key uniqueness. A `with unique keys` clause (JsonObjectAggUniqueKeysBehavior.WITH) would require deduplication the emulation does not implement, so render() throws QueryException up front. The same method also rejects filter combined with 'null on null'.","triggerScenarios":"HQL `json_objectagg(key e.code value e.name with unique keys)` on SingleStoreDialect; also json_objectagg with filter plus null on null throws the sibling message in the same method.","commonSituations":"Queries ported from Oracle/PostgreSQL where 'with unique keys' is honored; building maps from potentially non-unique keys and relying on the database to deduplicate.","solutions":["Drop 'with unique keys' and ensure the key expression is unique by construction (distinct subquery or grouping)","Deduplicate rows in application code and assemble the JSON object in Java","Use a native query that groups by the key before concatenation","Aggregate to a list of pairs in Java and build the map yourself, keeping the last/first value"],"exampleFix":"// before\nselect json_objectagg(key e.code value e.name with unique keys) from E e\n\n// after: key is unique by construction, clause dropped\nselect json_objectagg(key e.code value e.name) from E e","handlingStrategy":"fallback","validationCode":"// 'with unique keys' cannot be emulated on SingleStore\nstatic boolean objectAggEmulatable(boolean withUniqueKeys, Dialect d) {\n    return !(d instanceof SingleStoreDialect) || !withUniqueKeys;\n}","typeGuard":null,"tryCatchPattern":"try {\n    return em.createQuery(hql).getResultList(); // json_objectagg ... with unique keys\n} catch (QueryException e) {\n    if (e.getMessage() != null && e.getMessage().contains(\"unique keys\")) {\n        // drop the clause and deduplicate keys first (distinct/group by), then retry\n    }\n    throw e;\n}","preventionTips":["Guarantee key uniqueness in the query (distinct/group by) instead of the with-unique-keys clause","Assemble JSON maps in Java when duplicate keys are possible","Treat all json_objectagg behavior clauses as dialect-dependent and test per backend"],"tags":["hibernate","singlestore","json-objectagg","unique-keys"],"backgroundTag":"json-aggregate-emulation-unsupported","analyzedSha":"fad1729dce015f908198d57a8d80274a30f905a5","analyzedAt":"2026-08-22T04:13:57.527Z","schemaVersion":2},"datasetVersion":"2026-08-22T09:17:25.309Z"}