jOOQ JSON query results in ORA-00979

I’m trying to execute this query to an Oracle 19c database:

Field<JSON> employee = DSL.field("employee", JSON.class);

Table<Record1<JSON>> employees = dsl
            .select(jsonObject(jsonEntry("id", EMPLOYEE.ID), jsonEntry("name", EMPLOYEE.NAME), jsonEntry("phones",
                    jsonArrayAgg(
                            jsonObject(jsonEntry("number", PHONE.PHONENUMBER), jsonEntry("type", PHONE.TYPE)))
            )).as(employee))
            .from(EMPLOYEE)
            .join(PHONE).on(PHONE.EMPLOYEE_ID.eq(EMPLOYEE.ID))
            .groupBy(EMPLOYEE.ID)
            .asTable();

String json = dsl
            .select(jsonArrayAgg(employees.field(employee)))
            .from(employees)
            .fetchOneInto(String.class);

But I get

org.springframework.jdbc.BadSqlGrammarException: jOOQ; bad SQL grammar 
    [select json_arrayagg("alias_113372058".employee) from 
        (select json_object(key ? value "EMPLOYEE"."ID", key ? value "EMPLOYEE"."NAME", key ? value json_arrayagg(json_object(key ? value "PHONE"."PHONENUMBER", key ? value "PHONE"."TYPE"))) employee from "EMPLOYEE" join "PHONE" on "PHONE"."EMPLOYEE_ID" = "EMPLOYEE"."ID" group by "EMPLOYEE"."ID") "alias_113372058"]; 
nested exception is java.sql.SQLSyntaxErrorException: ORA-00979: Kein GROUP BY-Ausdruck

Does jOOQs JSON feature not work with Oracle?

Answer

This isn’t related to your JSON usage. The same thing would have happened if you removed all of it and wrote this query instead:

dsl.select(EMPLOYEE.ID, EMPLOYEE.NAME)
   .from(EMPLOYEE)
   .join(PHONE).on(PHONE.EMPLOYEE_ID.eq(EMPLOYEE.ID))
   .groupBy(EMPLOYEE.ID);

Your query would work in MySQL, PostgreSQL or standard SQL, where you can still project all functionally dependent columns after grouping by a primary key column. But in Oracle, this doesn’t work. So, you have to add EMPLOYEE.NAME to your GROUP BY clause.

There’s a feature request to transform your SQL accordingly, but jOOQ 3.14 does not support this yet: https://github.com/jOOQ/jOOQ/issues/4725

Note that JSON_ARRAYAGG() aggregates empty sets into NULL, not into an empty []. If that’s a problem, use COALESCE()