Using jOOQ multiset or row instead of jsonObject

Question

I currently use the below approach to reuse select statement. This approach works really well, but this approach doesn't provide me type safety as it is json function. Therefore, I would like to introduce the same pattern to select query with multiset for nested collection and row for nested single object without jsonObject.

data class FoodRecord(
  val id: UUID,
  val createdAt: Instant,
  val createdBy: UserGeneralRecord,
  val lastModifiedAt: Instant,
  val lastModifiedBy: UserGeneralRecord,
  val name: String
  val synonyms: List<String>
)

data class UserGeneralRecord(
  val id: UUID,
  val nickname: String,
  val role: UserRole
) {

  companion object {
    private val user = JUser.USER

    val select = jsonObject(
      key("id").value(user.ID),
      key("nickname").value(user.NICKNAME),
      key("role").value(user.ROLE)
    )
  }
}

  private val food: JFood = JFood.FOOD
  private val foodSynonym: JFoodSynonym = JFoodSynonym.FOOD_SYNONYM
  private val user: JUser = JUser.USER


  override fun fetch(query: FetchFoodByIdQuery): FoodRecord {
    return ctx.getContext()
      .select(
        jsonObject(
          key("id").value(food.ID),
          key("createdAt").value(food.CREATED_AT),
          key("createdBy").value(
            field(
              select(UserGeneralRecord.select)
                .from(user)
                .where(user.ID.eq(food.CREATED_BY))
            )
          ),
          key("lastModifiedAt").value(food.LAST_MODIFIED_AT),
          key("lastModifiedBy").value(
            field(
              select(UserGeneralRecord.select)
                .from(user)
                .where(user.ID.eq(food.LAST_MODIFIED_BY))
            )
          ),
          key("name").value(food.NAME),
          key("synonyms").value(
            field(
              select(jsonArrayAgg(foodSynonym.SYNONYM))
                .from(foodSynonym)
                .where(foodSynonym.FOOD_ID.eq(food.ID))
            )
          ),
        )
      )
      .from(food)
      .where(food.ID.eq(query.id))
      .fetchOneInto(FoodRecord::class.java)
      ?: throw NoResultFoundException("Food is not found by id: ${query.id}")
  }

I'm looking for a possible approach without using jsonObject as below(the below is not working, but only for showing my idea.)

override fun fetch(query: FetchFoodByIdQuery): FoodRecord {
    return ctx.getContext()
      .select(
        food.ID,
        food.CREATED_AT,
        row(
          select( // This select should be reused (same as lastModifiedBy)
            user.ID,
            user.NICKNAME,
            user.ROLE
          ).from(user)
            .where(user.ID.eq(food.CREATED_BY)) 
        ).`as`("createdBy"),
        food.LAST_MODIFIED_AT,
        row(
          select( // This select should be reused (same as createdBy)
            user.ID,
            user.NICKNAME,
            user.ROLE
          ).from(user)
            .where(user.ID.eq(food.LAST_MODIFIED_BY)) 
        ).`as`("lastModifiedBy"),
        multiset(
          select(foodSynonym.SYNONYM).from(foodSynonym)
            .where(foodSynonym.FOOD_ID.eq(food.ID))
        ).`as`("synonyms")
      )
      .from(food)
      .where(food.ID.eq(query.id))
      .fetchOneInto(FoodRecord::class.java)
      ?: throw NoResultFoundException("Food is not found by id: ${query.id}")
  }

Answer 1

This may just be a matter of understanding how to compose different elements to achieve what you want. Probably, this would work for you?

field(
  select(
    row(
      user.ID,
      user.NICKNAME,
      user.ROLE
    )
  ).from(user)
   .where(user.ID.eq(food.CREATED_BY)) 
).`as`("createdBy")

Incidentally, this is also how you'd do this with native PostgreSQL. There's no ROW(SELECT ..) constructor in PostgreSQL or standard SQL, like there's a MULTISET(SELECT ..) constructor.

If you want to reuse the row() expression, just assign it to a local variable or method, like you did in the jsonObject() case.