How to add another object in JSON with Ktor-CodePudding

I'm creating API server with Ktor and using exposed SQL.

I have two tables like below:

/// Performance Table
@kotlinx.serialization.Serializable
data class PerformanceDAO(val performanceId: String, val title: String, val actor: String)

object Performances : Table() {
    val performanceId: Column<String> = varchar("performanceId", 20)
    val title: Column<String> = varchar("title", 50)
    override val primaryKey = PrimaryKey(performanceId)
}

//Actor Table
object Actors: Table() {
    val performanceId: Column<String> = reference("performanceId", Performances.performanceId)
    val name: Column<String> = varchar("name", 10)
}

and I'm trying to get data this way:

class PerformanceDAOImpl : PerformanceDAOFacade {
    private fun resultRowToPerformance(row: ResultRow) = PerformanceDAO(
        performanceId = row[Performances.performanceId],
        title = row[Performances.title],
        actor = row[Actors.name],
    )

    override suspend fun allPerformances(): List<PerformanceDAO> = dbQuery {
        Actors.leftJoin(Performances)
            .slice(Actors.name, Performances.title, Performances.performanceId)
            .selectAll()
            .groupBy(Performances.title).map(::resultRowToPerformance)
    }
}

and then I got only one actors without a key.

    {
        "performanceId": "PF13234",
        "title": "Harry footer",
        "actor": [
            "John"
        ]
    },

but I want get data like this

{
        "performanceId": "PF13234",
        "title": "Harry footer",
        "actor": [
          {
            "name: "John",
            "image": "/upload/images/image.jpg"
          },
          {
            "name: "Harry",
            "image": "/upload/images/image.jpg"
          },

        ]
    },

I want to know how to make sub object in JSON with Exposed SQL!

CodePudding user response：

You have to create a separate data class ActorDTO and map it in your code by making subquery. Also, I would advice to make Exposed Entities and then map them to your DAO, it can be much simpler but will add some boilerplate code.

Please check documentation