jOOQ JSON query results in ORA-00979

Question

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 1

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()