I’m having trouble constructing the correct query for two levels of nested collections –

The data shape as JSON…

[
    {
        "name": "My Super Region",
        "regions": [
            {
                "name": "My Region",
                "locations": [
                    {
                        "name": "My Location"
                    }
                ]
            }
        ]
    }
]

I looked at the intermittent Result from my JOOQ query and the shape of the data seemed alright to me. However, I’m having trouble successfully mapping that result into a Kotlin data class.

There’s only so much that can be gleaned from the stack trace for something like this, so I’m having difficulty troubleshooting my cast errors. I’d appreciate any help sorting out what I’m missing!

fun getRegions(): List<SuperRegionRecord> {
    return DSL.using(dataSource, SQLDialect.MYSQL)
        .select(
            SUPER_REGIONS.ID,
            SUPER_REGIONS.NAME,
            multiset(
                select(
                    REGIONS.ID,
                    REGIONS.NAME,
                    multiset(
                        select(
                            LOCATIONS.ID,
                            LOCATIONS.NAME,
                        )
                        .from(LOCATIONS)
                        .where(LOCATIONS.REGION_ID.eq(REGIONS.ID))
                    ).`as`("locations")
                )
                .from(REGIONS)
                .where(REGIONS.SUPER_REGION_ID.eq(SUPER_REGIONS.ID))
            ).`as`("regions"),
        )
        .from(SUPER_REGIONS)
        .fetchInto(SuperRegionRecord::class.java)
}

data class SuperRegionRecord(
    val id: Int?,
    val name: String?,
    val regions: List<RegionRecord>?
)

data class RegionRecord(
    val id: Int?,
    val name: String?,
    val locations: List<LocationRecord>?
)

data class LocationRecord(
    val id: Int?,
    val name: String?
)

Error:
java.lang.ClassCastException: class org.jooq.impl.RecordImpl3 cannot be cast to class com.abcxyz.repository.LocationsRepository$RegionRecord (org.jooq.impl.RecordImpl3 is in unnamed module of loader 'app'; com.abcxyz.LocationsRepository$RegionRecord is in unnamed module of loader io.ktor.server.engine.OverridingClassLoader$ChildURLClassLoader @f68f0dc)