Nested Datatypes

ExoQuery naturally handles nested objects in your data classes by flattening them into SQL columns. This allows you to maintain clean, hierarchical data structures in your Kotlin code while seamlessly mapping them to flat database tables.

How Nested Objects Work

When you use nested objects in ExoQuery, the library flattens them by taking the innermost property names as SQL column names. The nested structure is preserved in your Kotlin types, but the SQL queries use only the leaf property names.

Querying Tables with Nested Fields

If your database table has columns named first, last, id, and age, you can model them using nested objects:

@Serializable
data class Name(val first: String, val last: String)

@Serializable
data class Person(val id: Int, val name: Name, val age: Int)

val q = sql { Table<Person>() }
q.buildFor.Postgres().runOn(myDatabase)
//> SELECT p.id, p.first, p.last, p.age FROM Person p

ExoQuery uses the innermost property names (first, last) as the SQL column names. When results are returned, they’re automatically mapped back into the nested Name object.

Mapping Flat Tables to Nested Structures

Sometimes your database has flat column names (like firstName, lastName) but you want to work with nested objects in your Kotlin code. You can use map to transform the flat data into a nested structure:

// The original Person table has flat columns: id, firstName, lastName, age
@Serializable
data class Person(val id: Int, val firstName: String, val lastName: String, val age: Int)

// The nested structure we want to work with
@Serializable
data class Name(val first: String, val last: String)

@Serializable
data class PersonWithName(val id: Int, val name: Name, val age: Int)

val q = sql { 
  Table<Person>().map { p -> 
    PersonWithName(p.id, Name(p.firstName, p.lastName), p.age) 
  } 
}
q.buildFor.Postgres().runOn(myDatabase)
//> SELECT p.id, p.firstName AS first, p.lastName AS last, p.age FROM Person p

ExoQuery automatically aliases the SQL columns (firstName AS first, lastName AS last) to match the nested structure. When the results are returned, they’re automatically mapped into the nested PersonWithName class.

Using @SerialName with Nested Objects

When your database column names differ from your property names, use @SerialName to specify the actual column names:

@Serializable
data class Name(
  @SerialName("name_first") val first: String, 
  @SerialName("name_last") val last: String
)

@Serializable
data class Person(val id: Int, val name: Name, val age: Int)

val q = sql { Table<Person>() }
q.buildFor.Postgres().runOn(myDatabase)
//> SELECT p.id, p.name_first, p.name_last, p.age FROM Person p

The @SerialName annotations on the leaf properties tell ExoQuery the actual column names in the database.

Nested Objects in Insert Operations

You can use nested objects in insert operations. ExoQuery uses the innermost property names when setting column values:

@Serializable
data class Name(val first: String, val last: String)

@Serializable
data class Person(val id: Int, val name: Name, val age: Int)

val q = sql {
  insert<Person> { 
    set(name.first to "Joe", name.last to "Bloggs", age to 123) 
  }.returning { p -> p.id to p.name }
}
q.buildFor.Postgres().runOn(myDatabase)
//> INSERT INTO Person (first, last, age) VALUES ('Joe', 'Bloggs', 123) 
//>   RETURNING id, first, last

Notice how you can directly reference nested properties like name.first and name.last in the set function, and the SQL uses the innermost names first and last.

Using Self-Referencing Nested Columns

You can reference nested columns using their own values in insert operations:

val q = sql {
  insert<Person> { 
    set(name.first to name.first + "_Suffix", name.last to "Bloggs", age to 123) 
  }.returning { p -> p.id to p.name }
}
q.buildFor.Postgres().runOn(myDatabase)
//> INSERT INTO Person (first, last, age) VALUES ((first || '_Suffix'), 'Bloggs', 123) 
//>   RETURNING id, first, last

Conflict Handling with Nested Columns

Nested columns work seamlessly with conflict handling operations:

@Serializable
data class Name(val first: String, val last: String)

@Serializable
data class Person(val id: Int, val name: Name, val age: Int)

// On conflict ignore
val q = sql {
  insert<Person> { 
    set(name.first to "Joe", name.last to "Bloggs", age to 123)
      .onConflictIgnore(name.first) 
  }
}
q.buildFor.Postgres().runOn(myDatabase)
//> INSERT INTO Person (first, last, age) VALUES ('Joe', 'Bloggs', 123) 
//>   ON CONFLICT (first) DO NOTHING

// On conflict update
val q2 = sql {
  insert<Person> { 
    set(name.first to "Joe", name.last to "Bloggs", age to 123)
      .onConflictUpdate(id) { excl -> 
        set(name.first to excl.name.first + "_Suffix") 
      } 
  }
}
q2.buildFor.Postgres().runOn(myDatabase)
//> INSERT INTO Person AS x (first, last, age) VALUES ('Joe', 'Bloggs', 123) 
//>   ON CONFLICT (id) DO UPDATE SET first = (EXCLUDED.first || '_Suffix')

Custom Naming with @ExoEntity and @ExoField

When you need to customize both table and column names (e.g., for quoted identifiers), you can use @ExoEntity and @ExoField annotations:

@Serializable
data class Name(
  @ExoField("FIRST") val first: String, 
  val last: String
)

@ExoEntity("PERSON")
@Serializable
data class Person(
  @ExoField("ID") val id: Int, 
  @ExoField("NAME") val name: Name, 
  val age: Int
)

val q = sql {
  insert<Person> { 
    set(name.first to "Joe", age to 123) 
  }.returning { p -> p.id to p.name }
}
q.buildFor.Postgres().runOn(myDatabase)
//> INSERT INTO "PERSON" ("NAME_FIRST", age) VALUES ('Joe', 123) RETURNING "ID", "NAME"

In this example:

• The @ExoEntity("PERSON") annotation sets the table name
• The @ExoField("NAME") on the name property provides a prefix for nested columns
• The @ExoField("FIRST") on first property combines with the prefix to produce NAME_FIRST

Note that the prefix behavior only happens when you use @ExoField on the nested property itself.

Deeply Nested Objects

ExoQuery handles multiple levels of nesting seamlessly. Consider an e-commerce system with orders, delivery info, and addresses:

@Serializable
data class Address(val street: String, val city: String)

@Serializable
data class Delivery(val carrier: String, val destination: Address)

@Serializable
data class Order(val orderId: Int, val total: Int, val delivery: Delivery)

val q = sql { 
  Table<Order>().filter { o -> o.delivery.destination.city == "Boston" }
}
q.buildFor.Postgres().runOn(myDatabase)
//> SELECT o.orderId, o.total, o.carrier, o.street, o.city 
//>   FROM Order o WHERE o.city = 'Boston'

You can access deeply nested properties directly using dot notation (o.delivery.destination.city), and ExoQuery resolves them to the correct column names. The triple-nested structure (Order → Delivery → Address) is fully preserved in your Kotlin code while the SQL works with flat columns.

Key Takeaways

• Innermost Names Only: ExoQuery uses only the innermost property names as SQL column names
• Preserved Structure: Your Kotlin code maintains clean nested structures while SQL remains flat
• Two Approaches: Model nested tables directly, or use map to transform flat tables into nested structures
• Explicit Renaming: Use @SerialName or @ExoField when column names differ from property names
• Full Feature Support: Nested objects work in queries, inserts, updates, conflicts, and complex operations
• Deep Nesting: Multiple levels of nesting are supported with automatic column aliasing in transformations

This approach gives you the best of both worlds: idiomatic Kotlin code with nested data structures, and efficient flat SQL queries.