How to fetch one-to-many nested data?

I'm working on a jOOQ query for MySQL where I need to fetch a single row from a main table along with one-to-many related data from two other tables. My schema consists of three tables:

TABLE_A: Contains a primary key (USER_ID) and a field NAME (plus other settings).
TABLE_B: Contains related values (e.g., B_VALUE) and a foreign key USER_ID.
TABLE_C: Contains related values (e.g., C_VALUE) and a foreign key USER_ID.

I need to map the result into the following POJOs:

class APojo {
    public Int userId;
    public List<String> listOfBValue;
    public List<String> listOfCValue;
}

The expected JSON output would be something like:

{
  "userId": 1,
  "listOfBValue": [ "bvalue1", "bvalue2" ],
  "listOfCValue": [ "cvalue1" ]
}

Initially, I tried writing the query inspired by this answer:

dslContext.select(
    TABLE_A.USER_ID,
    array(
        dslContext.select(row(TABLE_B.B_VALUE))
            .from(TABLE_B)
            .where(TABLE_B.USER_ID.eq(userId))
    ),
    array(
        dslContext.select(row(TABLE_C.C_VALUE))
            .from(TABLE_C)
            .where(TABLE_C.USER_ID.eq(userId))
    )
)
.from(TABLE_A)
.where(TABLE_A.USER_ID.eq(userId))
.fetchOne()

However, this query throws a "jOOQ; bad SQL grammar" exception. How to fix that?

Solution

MySQL doesn't have native support for ARRAY types, and as of jOOQ 3.19, there's no emulation for those operators using SQL/XML or SQL/JSON, as there is for MULTISET:

https://github.com/jOOQ/jOOQ/issues/12029

As such, just use MULTISET directly, which can be emulated using MySQL's JSON capabilities. The following example assumes you have the appropriate constructors (e.g. written manually or by switching to record classes), so you can use ad-hoc conversion:

ctx.select(
    TABLE_A.USER_ID,
    multiset(
        select(TABLE_B.B_VALUE)
        .from(TABLE_B)
        .where(TABLE_B.USER_ID.eq(userId))
    ).convertFrom(r -> r.map(Record1::value1))),
    multiset(
        select(TABLE_C.C_VALUE)
        .from(TABLE_C)
        .where(TABLE_C.USER_ID.eq(userId))
    ).convertFrom(r -> r.map(Record1::value1)))
)
.from(TABLE_A)
.where(TABLE_A.USER_ID.eq(userId))
.fetchOne(Records.mapping(APojo::new))

Or, since jOOQ 3.19, with implicit join path correlation available, you could also write this, instead:

ctx.select(
    TABLE_A.USER_ID,
    multiset(
        select(TABLE_A.tableB().B_VALUE)
        .from(TABLE_A.tableB()) // Implicit join path correlation
    ).convertFrom(r -> r.map(Record1::value1))),
    multiset(
        select(TABLE_A.tableC().C_VALUE)
        .from(TABLE_A.tableC()) // Implicit join path correlation
    ).convertFrom(r -> r.map(Record1::value1)))
)
.from(TABLE_A)
.where(TABLE_A.USER_ID.eq(userId))
.fetchOne(Records.mapping(APojo::new))

Note that alternatively, you can just use the JSON APIs directly