Exposed ORM Universal Filters

Type-safe, normalized filter model and utilities for building dynamic queries with JetBrains Exposed and Ktor.

Accept filters as JSON, parse them into a normalized filter tree, and safely apply them to Exposed queries.
Strong typing and SQL injection safety via Exposed DSL.
Flexible: use flat or nested (tree) filter structures with AND/OR/NOT combinators.
Modules:
- core – domain model (FilterOperator, FilterNode, normalization utils)
- jdbc – translation of filters to Exposed DSL predicates
- rest – Ktor helpers to parse HTTP JSON bodies into FilterRequest
Artifacts (Maven Central):
- ua.wwind.exposed-filters:exposed-filters-core:<version>
- ua.wwind.exposed-filters:exposed-filters-jdbc:<version>
- ua.wwind.exposed-filters:exposed-filters-rest:<version>

Installation
Compatibility
Quick start
DSL builder for FilterRequest
JSON serialization
Field naming in filters
Filtering by related entities (references)
Filtering by JSON/JSONB fields
Validation and errors
JSON request format
Supported operators
Type handling (JDBC adapter)
Excluding fields from filters
Custom column type mappers
Custom expression maps for complex queries
Example: filtering by date and timestamp
Why use this
CI and Release
License

Installation

Prerequisites: Kotlin 2.3.20, repository mavenCentral().

dependencies {
  implementation("ua.wwind.exposed-filters:exposed-filters-core:1.6.2")
  implementation("ua.wwind.exposed-filters:exposed-filters-jdbc:1.6.2")
  implementation("ua.wwind.exposed-filters:exposed-filters-rest:1.6.2")
}

Compatibility

Library version	Kotlin	Ktor	Exposed
1.6.1	2.3.20	3.4.2	1.2.0
1.5.0	2.3.20	3.4.1	1.1.1
1.4.0	2.3.0	3.3.3	1.0.0
1.3.0	2.3.0	3.3.2	1.0.0-rc-4
1.2.1	2.2.21	3.3.2	1.0.0-rc-4
1.2.0	2.2.20	3.3.1	1.0.0-rc-2
1.0.7	2.2.20	3.3.0	1.0.0-rc-1
1.0.1	2.2.0	3.2.3	1.0.0-beta-1

Quick start

1) Define your table (Exposed)

object Users : Table("users") {
    val id: Column<Int> = integer("id").autoIncrement()
    val name: Column<String> = varchar("name", 100)
    val age: Column<Int> = integer("age")
    override val primaryKey = PrimaryKey(id)
}

2) Ktor endpoint: receive filters and apply to query

routing {
    post("/users") {
        val filter = call.receiveFilterRequestOrNull()
        val result = transaction {
            Users
                .selectAll()
                .applyFiltersOn(Users, filter)
                .map { row ->
                    mapOf(
                        "id" to row[Users.id],
                        "name" to row[Users.name],
                        "age" to row[Users.age]
                    )
                }
        }
        call.respond(result)
    }
}

DSL builder for FilterRequest

Build FilterRequest objects programmatically using a type-safe Kotlin DSL. Useful for constructing filters in tests, backend services, or when you need to modify filters before applying them.

Basic usage

val filter = filterRequest {
  eq("status", "ACTIVE")
  gte("age", 18)
  contains("name", "John")
}

Infix syntax

Use infix functions for a more readable syntax:

val filter = filterRequest {
  "status" eq "ACTIVE"
  "age" gte 18
  "name" contains "John"
  "role" inList listOf("ADMIN", "USER")
  "score" between 0..100
}

Nested combinators

Create complex filter trees with and, or, and not blocks:

val filter = filterRequest {
  "active" eq true
  or {
    "role" eq "ADMIN"
    "role" eq "MODERATOR"
  }
  not {
    "status" eq "DELETED"
  }
}

All supported operators

val filter = filterRequest {
  // Equality
  eq("field", value)
  neq("field", value)

  // String operators
  contains("name", "substring")
  startsWith("name", "prefix")
  endsWith("name", "suffix")

  // Comparison
  gt("age", 18)
  gte("age", 18)
  lt("age", 65)
  lte("age", 65)

  // Collections
  inList("status", listOf("A", "B", "C"))
  notInList("status", listOf("DELETED", "ARCHIVED"))

  // Range
  between("age", 18, 65)
  "score" between (0 to 100)  // Pair syntax
  "year" between 2020..2024   // ClosedRange syntax

  // Nullability
  isNull("deletedAt")
  isNotNull("email")
  // Or extension syntax:
  "deletedAt".isNull()
  "email".isNotNull()
}

Nullable-aware operators

These operators remove the predicate when the value is null:

val filter = filterRequest {
  eqOrRemove("status", statusParam)       // adds EQ if not null, removes if null
  gteOrRemove("minAge", minAgeParam)
  inListOrRemove("tags", tagsParam)
  betweenOrRemove("date", fromDate, toDate)
}

Special case: add EQ or IS_NULL based on value:

val filter = filterRequest {
  eqOrIsNull("parentId", parentIdParam)  // EQ if not null, IS_NULL if null
}

Modifying existing filters

Convert an existing FilterRequest to a builder for modification:

val existingFilter: FilterRequest = call.receiveFilterRequestOrNull() ?: return

val modifiedFilter = existingFilter.toBuilder().apply {
  // Add a predicate if not already present
  eqIfAbsent("tenantId", currentTenantId)

  // Replace all predicates for a field
  replace("status", FilterOperator.IN, listOf("ACTIVE", "PENDING"))

  // Remove predicates for a field
  remove("internalField")
}.build()

JSON serialization

Serialize FilterRequest objects to JSON for caching, logging, or sending to other services.

Serialize to JSON

val filter = filterRequest {
  "status" eq "ACTIVE"
  "age" gte 18
  or {
    "role" eq "ADMIN"
    "role" eq "MODERATOR"
  }
}

val json = filter?.toJsonString()
// Produces JSON compatible with parseFilterRequestOrNull()

Round-trip serialization

The serialized JSON can be parsed back using the existing parseFilterRequestOrNull() function:

val original = filterRequest {
  "status" eq "ACTIVE"
  "department" inList listOf("IT", "HR")
}

// Serialize
val json = original?.toJsonString()

// Parse back (e.g., from cache or another service)
val restored = parseFilterRequestOrNull(json!!)

// Use restored filter
Users.selectAll().applyFiltersOn(Users, restored)

Use cases

Caching filters: Store frequently used filters in Redis or other caches
Audit logging: Log filter requests in a readable JSON format
Service communication: Send filters between microservices
Testing: Assert filter structure in unit tests

Field naming in filters

Always use the Kotlin Table property name (usually camelCase) in your JSON filters, not the physical DB column name.
This decouples your API from database naming conventions.

Example with a UUID foreign key:

object Warehouses : Table("warehouses") {
    val id: Column<UUID> = uuid("id")
}

object Products : Table("products") {
    val id: Column<Int> = integer("id").autoIncrement()
    val warehouseId: Column<UUID> = reference("warehouse_id", Warehouses.id) // property name vs DB column
}

Filter by property warehouseId (maps to DB warehouse_id):

{
  "filters": {
    "warehouseId": [ { "op": "EQ", "value": "11111111-1111-1111-1111-111111111111" } ]
  }
}

Notes:

Property names are resolved via reflection. This also works for enums, UUIDs, booleans, numbers, and strings.

Filtering by related entities (references)

You can filter by a field of a related entity using dot-paths on reference columns: referenceField.nestedField.
Under the hood, this is implemented via an EXISTS subquery against the referenced table.
Currently supports one-level nesting on reference columns.

Example: filter products by warehouse name prefix

{
  "filters": {
    "warehouseId.name": [ { "op": "STARTS_WITH", "value": "Cent" } ]
  }
}

Constraints:

Nested paths are allowed only on reference columns; using field.subField on a non-reference column raises an error.
Operator constraints still apply based on the target column type (e.g., CONTAINS/STARTS_WITH only for strings).
Path nesting for references is limited to one level (e.g., warehouseId.name). Multi-hop like a.b.c is not supported.

Filtering by JSON/JSONB fields

You can filter columns containing JSON or JSONB data using dot-path notation to access nested values within the JSON structure. The library uses Exposed's extract() with JSONPath to navigate into JSON objects.

Example: filter orders by customer city stored in a JSONB column

object Orders : Table("orders") {
  val id: Column<Int> = integer("id").autoIncrement()
  val payload: Column<Payload> = jsonb("payload", Json.Default) // JSONB column
}

Filter by nested JSON field:

{
  "filters": {
    "payload.customer.address.city": [
      {
        "op": "EQ",
        "value": "Kyiv"
      }
    ]
  }
}

Supported operators

All standard operators work with JSON fields:

Equality: EQ, NEQ
String search: CONTAINS, STARTS_WITH, ENDS_WITH
Sets: IN, NOT_IN
Ranges and comparisons: BETWEEN, GT, GTE, LT, LTE
Nullability: IS_NULL, IS_NOT_NULL

Type inference

The library automatically infers the JSON value type based on the raw string:

Numbers: values like 123, 45.67 are treated as Double
Booleans: true or false (parsed with toBooleanStrict())
Dates: YYYY-MM-DD format → SQL DATE
Timestamps: YYYY-MM-DDTHH:MM or YYYY-MM-DDTHH:MM:SS → SQL TIMESTAMP
Strings: any other value

Example with multiple operators:

{
  "filters": {
    "payload.status": [
      {
        "op": "EQ",
        "value": "active"
      }
    ],
    "payload.amount": [
      {
        "op": "GT",
        "value": "100"
      }
    ],
    "payload.tags": [
      {
        "op": "CONTAINS",
        "value": "vip"
      }
    ],
    "payload.createdAt": [
      {
        "op": "GTE",
        "value": "2024-01-01T00:00:00"
      }
    ]
  }
}

Constraints:

JSON path filtering only works on columns marked with JsonColumnMarker (JSON/JSONB columns in Exposed)
CONTAINS, STARTS_WITH, ENDS_WITH only work with string JSON values
BETWEEN is not supported for boolean JSON values
Comparison operators (GT, GTE, LT, LTE) are not supported for boolean JSON values

Validation and errors

Requests that reference unknown fields will fail fast with a clear error message, e.g. Unknown filter field: foo.
In the sample Ktor app, such cases are mapped to HTTP 400 Bad Request via a global StatusPages handler.

JSON request format

You can send a flat structure or a nested tree. Both parse into a FilterRequest.

Flat (single level)

{
  "combinator": "AND",
  "filters": {
    "name": [{ "op": "CONTAINS", "value": "Al" }],
    "age":  [{ "op": "GTE", "value": "18" }]
  }
}

Tree (nested groups)

{
  "combinator": "OR",
  "children": [
    { "filters": { "age": [{ "op": "BETWEEN", "values": ["18", "25"] }] } },
    { "filters": { "name": [{ "op": "STARTS_WITH", "value": "Da" }] } }
  ]
}

Multi-level nested groups (AND/OR/NOT)

Arbitrary depth is supported for grouping with AND/OR/NOT combinators. You can nest groups within children to any level to express complex logic.

{
  "combinator": "AND",
  "children": [
    {
      "filters": {
        "status": [{ "op": "IN", "values": ["ACTIVE", "PENDING"] }]
      }
    },
    {
      "combinator": "OR",
      "children": [
        { "filters": { "name": [{ "op": "CONTAINS", "value": "Ann" }] } },
        {
          "combinator": "AND",
          "children": [
            { "filters": { "age": [{ "op": "GTE", "value": "21" }] } },
            { "filters": { "age": [{ "op": "LT", "value": "30" }] } }
          ]
        }
      ]
    }
  ]
}

NOT groups

Negate a group by using combinator: "NOT". All children at that level are combined with AND and wrapped in NOT.

{
  "combinator": "NOT",
  "children": [
    {
      "filters": {
        "status": [
          {
            "op": "EQ",
            "value": "DELETED"
          }
        ]
      }
    },
    {
      "filters": {
        "archived": [
          {
            "op": "EQ",
            "value": "true"
          }
        ]
      }
    }
  ]
}

Notes:

Nested children can be combined with AND or OR at each level. This supports many levels of nesting.
Single-child groups are normalized away; empty groups are ignored.
A NOT group with a single child is preserved (not flattened) and negates that child's conjunction.
This is independent from nested field paths for references, which remain limited to one level (see next section).

Supported operators

Equality: EQ, NEQ
String search: CONTAINS, STARTS_WITH, ENDS_WITH
Sets: IN, NOT_IN (including array and JSON fields)
Ranges and comparisons: BETWEEN, GT, GTE, LT, LTE
Nullability: IS_NULL, IS_NOT_NULL

Type handling (JDBC adapter)

Automatic conversion from JSON strings to column types:

Int, Long, Short, Double
String (VarChar, Text) with LIKE support for the string operators
UUID
Boolean (toBooleanStrict(): only "true"/"false")
Enums (by enum constant name)
Date and time
- Date-only (LocalDate): supports both Java java.time.LocalDate and kotlinx.datetime.LocalDate backed columns
- Timestamp/DateTime: supports LocalDateTime, Instant (including kotlin.time.Instant), and SQL TIMESTAMP
- Accepted formats in JSON
  - Date: YYYY-MM-DD
  - Timestamp: ISO date-time with optional seconds, YYYY-MM-DDTHH:MM or YYYY-MM-DDTHH:MM:SS (a space instead of T is allowed). A date-only value for a timestamp column is treated as start of day (00:00:00)

Operator constraints:

LIKE-style operators only for string columns.
BETWEEN requires exactly two values; not supported for UUID/Enum/Boolean.
IN requires non-empty values; NOT_IN with an empty values array is treated as a no-op (ignored).
IN and NOT_IN are supported for scalar and array fields.
Date/time operators: EQ, IN, BETWEEN, GT, GTE, LT, LTE, IS_NULL, IS_NOT_NULL are supported for date and timestamp columns.

Excluding fields from filters

When building complex queries with joins, subqueries, or multi-level data aggregation, you may need to apply the same user filter at different query levels. However, not all fields may be available at every level — some columns might only exist in the final result set, or certain fields should not constrain intermediate queries.

The excludeFields utility allows you to safely remove specific field conditions from a FilterRequest while preserving correct filtering semantics. The key guarantee is that excluding fields will never narrow the result set — it may only broaden it. This is safe because you can apply the full, unmodified filter on the final result.

How it works

The behavior depends on the logical combinator:

AND: Removing a field broadens the result set (safe). The remaining conditions still apply.
OR: Removing a field from an OR group would narrow results (records matching only the removed condition would be lost). Therefore, the entire OR group is removed if any direct leaf contains an excluded field.
NOT: Due to De Morgan's law, removing fields inside a NOT group can narrow results. Therefore, the entire NOT group is removed if any child at any depth contains an excluded field.

Usage

val filter: FilterRequest = ...

// Exclude specific fields
val partialFilterForTable1 = filter.excludeFields("profileId")
val partialFilterForTable2 = filter.excludeFields("userId")

// Use partial filter for intermediate query (missing some columns)
val intermediateQuery1 = SomeTable1
        .selectAll()
        .applyFiltersOn(SomeTable1, partialFilterForTable1)

val intermediateQuery2 = SomeTable2
        .selectAll()
        .applyFiltersOn(SomeTable2, partialFilterForTable2)


// Apply full filter on the final result
val finalQuery =
    (intermediateQuery1 union intermediateQuery2)
        .selectAll()

finalQuery
        .applyFiltersOn(finalQuery, filter)

Example scenario

Imagine a query that joins Orders with OrderItems and computes a totalAmount field. The user sends a filter:

{
  "combinator": "AND",
  "filters": {
    "status": [{ "op": "EQ", "value": "COMPLETED" }],
    "totalAmount": [{ "op": "GTE", "value": "1000" }]
  }
}

The totalAmount field only exists after aggregation, so you cannot apply it to the base Orders query. Using excludeFields("totalAmount") removes that condition for the intermediate query while keeping the status filter. After aggregation, you apply the full filter including totalAmount on the final dataset.

Custom column type mappers

For custom column types not covered by built-in support, you can provide value mappers using ColumnMappersModule. This allows you to register multiple mappers and apply them to filter operations.

Using the DSL

Create a mappers module with the columnMappers DSL function:

val customMappers = columnMappers {
    // Add a mapper using a lambda
    mapper { columnType, rawValue ->
        when {
            columnType is BigDecimalColumnType -> BigDecimal(rawValue)
            columnType.javaClass.simpleName == "JsonColumnType" -> Json.parse(rawValue)
            else -> null // return null if this mapper doesn't handle the type
        }
    }
    
    // Add another mapper for different types
    mapper { columnType, rawValue ->
        if (columnType is MyCustomType) {
            parseMyCustomValue(rawValue)
        } else null
    }
    
    // Or add a pre-built mapper object
    addMapper(MyCustomMapper())
}

Applying mappers to queries

Pass the mappers module as the third argument to applyFiltersOn:

routing {
    post("/products") {
        val filter = call.receiveFilterRequestOrNull()
        val result = transaction {
            Products
                .selectAll()
                .applyFiltersOn(Products, filter, customMappers)
                .map { /* ... */ }
        }
        call.respond(result)
    }
}

How mappers work

Custom mappers are tried first: Mappers registered in the module are tried in reverse order (last registered first), giving priority to the most recently added mappers.
Built-in mappers as fallback: If no custom mapper handles the type (returns null), the library falls back to built-in type handling for standard types (Int, Long, String, UUID, Date, etc.).
Error on unsupported types: If neither custom nor built-in mappers handle the type, an error is thrown.

This design allows you to override built-in behavior for standard types if needed, while still providing sensible defaults.

Implementing a reusable mapper

You can implement the ColumnValueMapper interface for reusable mappers:

class BigDecimalMapper : ColumnValueMapper {
    override fun <T : Any> map(columnType: IColumnType<T>, raw: String): T? {
        return if (columnType is BigDecimalColumnType) {
            @Suppress("UNCHECKED_CAST")
            BigDecimal(raw) as T
        } else null
    }
}

// Usage:
val mappers = columnMappers {
    addMapper(BigDecimalMapper())
}

Example: JSON column support

val mappers = columnMappers {
    mapper { columnType, rawValue ->
        if (columnType.javaClass.simpleName.contains("Json")) {
            // Parse JSON string into your JSON object type
            kotlinx.serialization.json.Json.parseToJsonElement(rawValue)
        } else null
    }
}

// Apply to query
MyTable
    .selectAll()
    .applyFiltersOn(MyTable, filterRequest, mappers)

Custom expression maps for complex queries

When working with complex queries involving unions, joins, or computed expressions, you may need to explicitly map field names to specific SQL expressions rather than relying on the default table column lookup. This is useful when:

Union queries: combining results from multiple sources where field values come from different tables or expressions
Aliased expressions: using computed fields, literal values, or expressions with aliases
Joined queries: selecting columns from multiple related tables in a single result set

Basic approach

Instead of using applyFiltersOn(table, filter), use the lower-level applyFilters(expressionMap, filter) method and provide an explicit mapping from field names to SQL expressions.

Example: Union query with mixed sources

Consider a query that combines employees and contractors into a unified result set:

object Employees : Table("employees") {
    val id = integer("id")
    val name = varchar("name", 100)
    val salary = integer("salary")
    val departmentId = integer("department_id")
}

object Contractors : Table("contractors") {
    val id = integer("id")
    val name = varchar("name", 100)
    val hourlyRate = integer("hourly_rate")
    val projectId = integer("project_id")
}

// Build column mappings for union query
fun buildWorkersQuery(filter: FilterRequest?): Query {
    // Map property names to expressions for employees
    val employeeColumns = linkedMapOf(
        "id" to Employees.id.alias("id"),
        "name" to Employees.name.alias("name"),
        "rate" to Employees.salary.alias("rate"),
        "assignmentId" to Employees.departmentId.alias("assignmentId"),
        "type" to stringLiteral("EMPLOYEE").alias("type")  // computed field
    )
    
    // Map property names to expressions for contractors
    val contractorColumns = linkedMapOf(
        "id" to Contractors.id.alias("id"),
        "name" to Contractors.name.alias("name"),
        "rate" to Contractors.hourlyRate.alias("rate"),
        "assignmentId" to Contractors.projectId.alias("assignmentId"),
        "type" to stringLiteral("CONTRACTOR").alias("type")
    )
    
    // Build and union queries with filters applied
    val employeeQuery = Employees
        .select(employeeColumns.values.toList())
        .applyFilters(employeeColumns, filter)
    
    val contractorQuery = Contractors
        .select(contractorColumns.values.toList())
        .applyFilters(contractorColumns, filter)
    
    val unionAlias = employeeQuery.unionAll(contractorQuery).alias("workers")
    
    // Map union result columns to property names
    val finalMapping = employeeColumns.keys.withIndex().associate { (index, propertyName) ->
        propertyName to unionAlias.fields[index] as ExpressionWithColumnType<*>
    }
    
    return unionAlias.selectAll().applyFilters(finalMapping, filter)
}

Key points

LinkedHashMap preserves order: Use LinkedHashMap to ensure column order matches between queries in a union
Consistent property names: Both sub-queries must map the same property names, even if they source from different columns
Computed expressions: You can include literal values, calculated fields, or any Exposed expression
Apply filters at each level: Filters can be applied to individual queries before union, and again to the final result
Position-based mapping: After union, map union alias fields by position to property names

Filtering the result

Users can filter the unified result using property names from the expression map:

{
  "filters": {
    "type": [{ "op": "EQ", "value": "EMPLOYEE" }],
    "rate": [{ "op": "GTE", "value": "50000" }],
    "name": [{ "op": "CONTAINS", "value": "John" }]
  }
}

The filter will be correctly applied whether the record comes from Employees or Contractors, because both map to the same unified property names.

Integration with custom mappers

Custom expression maps work seamlessly with custom column type mappers:

val query = buildWorkersQuery(filter)
    .applyFilters(expressionMap, filter, customColumnMappers)

This allows you to handle both complex query structures and custom column types in the same query.

Example: filtering by date and timestamp

The example module contains an Events table demonstrating both a date-only field and a timestamp field, along with a POST /events endpoint that accepts filters:

{
  "filters": {
    "day": [ { "op": "EQ", "value": "2024-01-01" } ],
    "occurredAt": [ { "op": "GTE", "value": "2024-07-01T00:00:00" } ]
  }
}

Why use this

Clear separation: request format → normalized model → SQL predicates.
Safer queries by leveraging Exposed DSL.
Reusable across endpoints; supports both simple and complex trees of conditions.

CI and Release

CI builds on pushes and PRs against main.
Releases are published to Maven Central on pushing tags v*.

Name		Name	Last commit message	Last commit date
Latest commit History 56 Commits
.github/workflows		.github/workflows
core		core
example		example
gradle		gradle
jdbc		jdbc
rest		rest
.gitignore		.gitignore
CHANGELOG.md		CHANGELOG.md
CLAUDE.md		CLAUDE.md
LICENSE		LICENSE
README.md		README.md
RELEASING.md		RELEASING.md
build.gradle.kts		build.gradle.kts
gradle.properties		gradle.properties
gradlew		gradlew
gradlew.bat		gradlew.bat
settings.gradle.kts		settings.gradle.kts

Folders and files

Latest commit

History

Repository files navigation

Exposed ORM Universal Filters

Table of Contents

Installation

Compatibility

Quick start

1) Define your table (Exposed)

2) Ktor endpoint: receive filters and apply to query

DSL builder for FilterRequest

Basic usage

Infix syntax

Nested combinators

All supported operators

Nullable-aware operators

Modifying existing filters

JSON serialization

Serialize to JSON

Round-trip serialization

Use cases

Field naming in filters

Filtering by related entities (references)

Filtering by JSON/JSONB fields

Supported operators

Type inference

Validation and errors

JSON request format

Flat (single level)

Tree (nested groups)

Multi-level nested groups (AND/OR/NOT)

NOT groups

Supported operators

Type handling (JDBC adapter)

Excluding fields from filters

How it works

Usage

Example scenario

Custom column type mappers

Using the DSL

Applying mappers to queries

How mappers work

Implementing a reusable mapper

Example: JSON column support

Custom expression maps for complex queries

Basic approach

Example: Union query with mixed sources

Key points

Filtering the result

Integration with custom mappers

Example: filtering by date and timestamp

Why use this

CI and Release

License

About

Topics

Resources

License

Uh oh!

Stars

Watchers

Forks

Releases 19

Contributors

Uh oh!

Languages