Query Operations

Basic Operations

Map and Filter operations for querying data

Map

This is also known as an SQL projection. It allows you to select a subset of the columns in a table.

val q = sql {
  Table<Person>().map { p -> p.name }
}
q.buildFor.Postgres().runOn(myDatabase)
//> SELECT p.id, p.name FROM Person p

You can also use the .map funtion to perform simple aggreations on tables. For example:

val q = sql {
  Table<Person>().map { p -> Triple(min(p.age), max(p.age), avg(p.age)) }
}
q.buildFor.Postgres().runOn(myDatabase)
//> SELECT min(p.age), max(p.age), avg(p.age) FROM Person p

FlatMap

The flatMap operation lets you compose queries by taking each row from one query and producing a new query based on it. It's an alternative to explicit joins that can be more intuitive for certain query patterns.

Basic Usage

val q = sql {
  Table<Person>().flatMap { p ->
    Table<Address>().filter { a -> a.ownerId == p.id }.map { a -> p.name to a.city }
  }
}
//> SELECT p.name AS first, a.city AS second FROM Person p, Address a WHERE a.owner_id = p.id

When to Use FlatMap vs Joins

Use flatMap when:

You want to express a relationship in a more functional style
The relationship feels like "for each person, get their addresses"
You're composing multiple queries sequentially

Use explicit joins when:

You need LEFT JOIN or other join types
The relationship is complex with multiple tables
You prefer declarative SQL-style syntax

Example: Nested Relationships

// Find all orders with their items for customers in a specific city
val q = sql {
  Table<Customer>().filter { c -> c.city == "NYC" }.flatMap { c ->
    Table<Order>().filter { o -> o.customerId == c.id }.flatMap { o ->
      Table<OrderItem>().filter { i -> i.orderId == o.id }.map { i ->
        Triple(c.name, o.orderNumber, i.productName)
      }
    }
  }
}

Count and Count Distinct

Use count() to perform a COUNT(*) query:

val peopleCount = sql {
  Table<Person>().map { count() }
}
peopleCount.buildFor.Postgres().runOn(myDatabase)
//> SELECT count(*) AS value FROM Person it

You can also count a specific column (this counts non-NULL values in that column):

val nameCount = sql {
  Table<Person>().map { p -> count(p.name) }
}
nameCount.buildFor.Postgres().runOn(myDatabase)
//> SELECT count(p.name) AS value FROM Person p

Or in a select block:

val peopleCount = sql.select {
  val p = from(Table<Person>())
  count()
}
peopleCount.buildFor.Postgres().runOn(myDatabase)
//> SELECT count(*) AS value FROM Person p

You can combine count(column) with other columns in a custom return type:

data class CityStats(val cityName: String, val emailCount: Int)

val cityStats = sql.select {
  val p = from(Table<Person>())
  groupBy(p.city)
  CityStats(p.city, count(p.email))
}
cityStats.buildFor.Postgres().runOn(myDatabase)
//> SELECT p.city AS cityName, count(p.email) AS emailCount FROM Person p GROUP BY p.city

Use countDistinct to count unique values in a column:

val q = sql {
  Table<Person>().map { p -> countDistinct(p.name) }
}
q.buildFor.Postgres().runOn(myDatabase)
//> SELECT count(DISTINCT p.name) AS value FROM Person p

Or in a select block:

val distinctNames = sql.select {
  val p = from(Table<Person>())
  countDistinct(p.name)
}
distinctNames.buildFor.Postgres().runOn(myDatabase)
//> SELECT COUNT(DISTINCT p.name) FROM Person p

For databases that support it (such as PostgreSQL), you can count distinct combinations of multiple columns:

val q = sql {
  Table<Person>().map { p -> countDistinct(p.name, p.age) }
}
q.buildFor.Postgres().runOn(myDatabase)
//> SELECT count(DISTINCT p.name, p.age) AS value FROM Person p

Note: Support for multi-column countDistinct depends on the database. While PostgreSQL supports counting distinct combinations of multiple columns natively, other databases (like MySQL and SQLite) do not. For databases that don't support this feature, you can concatenate columns instead:

val q = sql {
  Table<Person>().map { p -> countDistinct(p.name + "," + p.age) }
}
q.buildFor.MySQL().runOn(myDatabase)
//> SELECT count(DISTINCT p.name || ',' || p.age) AS value FROM Person p

Min and Max

Use min() and max() to find the minimum and maximum values in a column:

val q = sql {
  Table<Person>().map { p -> Pair(min(p.age), max(p.age)) }
}
q.buildFor.Postgres().runOn(myDatabase)
//> SELECT MIN(p.age), MAX(p.age) FROM Person p

Or in a select block:

val minAge = sql.select {
  val p = from(Table<Person>())
  min(p.age)
}
minAge.buildFor.Postgres().runOn(myDatabase)
//> SELECT MIN(p.age) FROM Person p

Sum

Use sum() to calculate the total of a numeric column:

val totalAge = sql {
  Table<Person>().map { p -> sum(p.age) }
}
totalAge.buildFor.Postgres().runOn(myDatabase)
//> SELECT SUM(p.age) FROM Person p

Or in a select block:

val totalAge = sql.select {
  val p = from(Table<Person>())
  sum(p.age)
}
totalAge.buildFor.Postgres().runOn(myDatabase)
//> SELECT SUM(p.age) FROM Person p

Avg (Average)

Use avg() to calculate the average of a numeric column. By default, it returns a Double:

val averageAge = sql {
  Table<Person>().map { p -> avg(p.age) }
}
averageAge.buildFor.Postgres().runOn(myDatabase)
//> SELECT AVG(p.age) FROM Person p

You can specify a custom return type using the generic parameter. This is useful on the JVM when you need precision types like BigDecimal:

val preciseAverage = sql {
  Table<Person>().map { p -> avg<BigDecimal>(p.age) }
}
preciseAverage.buildFor.Postgres().runOn(myDatabase)
//> SELECT AVG(p.age) FROM Person p

Note: Custom return types like BigDecimal are JVM-specific and not available in Kotlin Multiplatform (KMP) projects.

Stddev (Standard Deviation)

Use stddev() to calculate the standard deviation of a numeric column. By default, it returns a Double:

val ageStdDev = sql {
  Table<Person>().map { p -> stddev(p.age) }
}
ageStdDev.buildFor.Postgres().runOn(myDatabase)
//> SELECT STDDEV(p.age) FROM Person p

Like avg(), you can specify a custom return type for precision:

val preciseStdDev = sql {
  Table<Person>().map { p -> stddev<BigDecimal>(p.age) }
}
preciseStdDev.buildFor.Postgres().runOn(myDatabase)
//> SELECT STDDEV(p.age) FROM Person p

Note: Custom return types like BigDecimal are JVM-specific and not available in Kotlin Multiplatform (KMP) projects.

Combining Multiple Aggregators

You can combine multiple aggregation functions in a single query using a custom return type:

data class OrderStats(
  val totalOrders: Int,
  val uniqueCustomers: Int,
  val totalRevenue: Double,
  val avgOrderValue: Double,
  val minOrderValue: Double,
  val maxOrderValue: Double,
  val hasLowActivity: Boolean
)

val stats = sql.select {
  val o = from(Table<Order>())
  OrderStats(
    count(),
    countDistinct(o.customerId),
    sum(o.amount),
    avg(o.amount),
    min(o.amount),
    max(o.amount),
    if (count() < 10) true else false
  )
}
stats.buildFor.Postgres().runOn(myDatabase)
//> SELECT
//>   count(*) AS totalOrders,
//>   sum(o.amount) AS totalRevenue,
//>   avg(o.amount) AS avgOrderValue,
//>   min(o.amount) AS minOrderValue,
//>   max(o.amount) AS maxOrderValue,
//>   CASE WHEN count(*) < 10 THEN 1 ELSE 0 END AS hasLowActivity
//> FROM "Order" o

This allows you to gather comprehensive statistics in a single database query, including conditional logic based on aggregated values.

Filter

This is also known as a SQL where clause. It allows you to filter the rows in a table.

val q = sql {
  Table<Person>().filter { p -> p.name == "Joe" }
}
q.buildFor.Postgres().runOn(myDatabase)
//> SELECT p.id, p.name, p.age FROM Person p WHERE p.name = 'Joe'

Also, if you are using a sql.select block, you can also use the where function to filter the rows:

val q = sql.select {
  val p = from(Table<Person>())
  where { p.name == "Joe" }
  p
}

Conditional Expressions

ExoQuery translates Kotlin's native conditional expressions directly into SQL CASE statements. There is no special case() function.

If/Else

val q = sql {
  Table<Order>().map { o ->
    if (o.amount > 100) "high" else "low"
  }
}
//> SELECT CASE WHEN o.amount > 100 THEN 'high' ELSE 'low' END AS value FROM Order o

When (multiple branches)

val q = sql {
  Table<Order>().map { o ->
    when {
      o.amount > 1000 -> "premium"
      o.amount > 100 -> "standard"
      else -> "budget"
    }
  }
}
//> SELECT CASE WHEN o.amount > 1000 THEN 'premium' WHEN o.amount > 100 THEN 'standard' ELSE 'budget' END AS value FROM Order o

Nullable Coalescing

val q = sql {
  Table<Person>().map { p ->
    p.middleName ?: "N/A"
  }
}
//> SELECT CASE WHEN p.middleName IS NULL THEN 'N/A' ELSE p.middleName END AS value FROM Person p