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

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.

Source

Thrown at hibernate-core/src/main/java/org/hibernate/dialect/function/json/DB2JsonObjectAggFunction.java:37

/**
 * DB2 json_objectagg function.
 */
public class DB2JsonObjectAggFunction extends JsonObjectAggFunction {

	public DB2JsonObjectAggFunction(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( "'{'||listagg(" );
		renderArgument( sqlAppender, arguments.key(), arguments.nullBehavior(), 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)

Solutions

1. Drop the with unique keys clause
2. Guarantee uniqueness upstream: aggregate over a query that already deduplicates keys (distinct, group by), and validate duplicates in application code
3. Use a native DB2 query if duplicate keys must be detected at query time

Example fix

// before - throws on DB2
select json_objectagg(key i.sku value i.name with unique keys) from OrderItem i

// after - no unique-keys enforcement; deduplicate inputs yourself
select json_objectagg(key i.sku value i.name) from OrderItem i

// Reject WITH UNIQUE KEYS for json_objectagg on DB2 before execution
static void assertTranslatable(SessionFactory sf, String hql) {
    if (sf.getJdbcServices().getDialect() instanceof org.hibernate.dialect.DB2Dialect
            && hql.toLowerCase().contains("with unique keys")) {
        throw new IllegalArgumentException(
            "DB2 emulation cannot enforce 'with unique keys'; deduplicate keys upstream");
    }
}

try {
    return session.createQuery(hql, String.class).getSingleResult();
} catch (org.hibernate.QueryException e) {
    if (e.getMessage() != null && e.getMessage().contains("'with unique keys' clause")) {
        // Retry without enforcement; duplicates must be prevented by the data
        return session.createQuery(hql.replace("with unique keys", ""), String.class).getSingleResult();
    }
    throw e;
}

When it happens

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

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

Related errors

• Can't emulate json_objectagg filter clause when using 'null
• Can't emulate json_arrayagg filter clause when using 'null o
• Can't emulate null on error clause on DB2
• Can't emulate json_objectagg filter clause when using 'null
• Can't emulate on error clause on CockroachDB

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