hibernate/hibernate-orm · error · QueryException

Can't emulate json_objectagg 'with unique keys' clause.

Error message

Can't emulate json_objectagg 'with unique keys' clause.

What it means

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'.

Solutions

  1. Drop 'with unique keys' and ensure the key expression is unique by construction (distinct subquery or grouping)
  2. Deduplicate rows in application code and assemble the JSON object in Java
  3. Use a native query that groups by the key before concatenation
  4. Aggregate to a list of pairs in Java and build the map yourself, keeping the last/first value

Example fix

// before
select json_objectagg(key e.code value e.name with unique keys) from E e

// after: key is unique by construction, clause dropped
select json_objectagg(key e.code value e.name) from E e
Defensive patterns

Strategy: fallback

Validate before calling

// 'with unique keys' cannot be emulated on SingleStore
static boolean objectAggEmulatable(boolean withUniqueKeys, Dialect d) {
    return !(d instanceof SingleStoreDialect) || !withUniqueKeys;
}

Try / catch

try {
    return em.createQuery(hql).getResultList(); // json_objectagg ... with unique keys
} catch (QueryException e) {
    if (e.getMessage() != null && e.getMessage().contains("unique keys")) {
        // drop the clause and deduplicate keys first (distinct/group by), then retry
    }
    throw e;
}

Prevention

When it happens

Trigger: 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.

Common situations: 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.

Related errors

AI-assisted analysis of hibernate/hibernate-orm@fad1729dce (2026-08-22). Data as JSON: /api/errors/248eab569577f82d. Report an issue: GitHub.

Appendix: source

Thrown at hibernate-community-dialects/src/main/java/org/hibernate/community/dialect/function/json/SingleStoreJsonObjectAggFunction.java:37

/**
 * SingleStore json_objectagg function.
 */
public class SingleStoreJsonObjectAggFunction extends JsonObjectAggFunction {

	public SingleStoreJsonObjectAggFunction(TypeConfiguration typeConfiguration) {
		super( ",", false, typeConfiguration );
	}

	@Override
	protected void render(
			SqlAppender sqlAppender,
			JsonObjectAggArguments arguments,
			Predicate filter,
			ReturnableType<?> returnType,
			SqlAstTranslator<?> translator) {
		final boolean caseWrapper = filter != null;
		if ( arguments.uniqueKeysBehavior() == JsonObjectAggUniqueKeysBehavior.WITH ) {
			throw new QueryException( "Can't emulate json_objectagg 'with unique keys' clause." );
		}
		sqlAppender.appendSql( "concat('{',group_concat(concat(to_json(" );
		arguments.key().accept( translator );
		sqlAppender.appendSql( "),':'," );
		if ( caseWrapper ) {
			if ( arguments.nullBehavior() != JsonNullBehavior.ABSENT ) {
				throw new QueryException( "Can't emulate json_objectagg filter clause when using 'null on null' clause." );
			}
			translator.getCurrentClauseStack().push( Clause.WHERE );
			sqlAppender.appendSql( "case when " );
			filter.accept( translator );
			translator.getCurrentClauseStack().pop();
			sqlAppender.appendSql( " then " );
			renderArgument( sqlAppender, arguments.value(), arguments.nullBehavior(), translator );
			sqlAppender.appendSql( " else null end)" );
		}
		else {
			renderArgument( sqlAppender, arguments.value(), arguments.nullBehavior(), translator );

View on GitHub (pinned to fad1729dce)