Data Handling

JSON Columns

Working with JSON and JSONB columns in PostgreSQL

Note: JSON column support is currently only available for PostgreSQL.

ExoQuery provides support for working with JSON and JSONB columns in PostgreSQL. You can select entire JSON objects, query specific fields, and insert JSON data using regular Kotlin data classes.

Setup

To use JSON columns, annotate your Kotlin data class with @SqlJsonValue. This tells ExoQuery to serialize/deserialize the class as JSON when interacting with the database:

@SqlJsonValue
@Serializable
data class ContactInfo(val email: String, val phone: String)

@Serializable
data class User(val id: Int, val name: String, val contacts: ContactInfo)

Database Schema

Your PostgreSQL table can use either JSON or JSONB column types:

CREATE TABLE Users (
    id       SERIAL PRIMARY KEY,
    name     VARCHAR(255),
    contacts JSONB
);

Selecting Entire Rows

You can select entire rows including JSON columns, and ExoQuery will automatically deserialize the JSON data:

val users = sql {
  Table<User>()
}.buildFor.Postgres().runOn(ctx)
//> SELECT id, name, contacts FROM Users
//> [User(1, "Alice", ContactInfo("alice@example.com", "555-1234"))]

Selecting JSON Fields

You can map to just the JSON field, which will be deserialized into your Kotlin object:

val contacts = sql {
  Table<User>().map { it.contacts }
}.buildFor.Postgres().runOn(ctx)
//> SELECT contacts FROM Users
//> [ContactInfo("alice@example.com", "555-1234")]

Implicit JSON Field Extraction

ExoQuery can implicitly extract individual fields from JSON columns when the column's Kotlin type is annotated with @SqlJsonValue.

You can write normal Kotlin property access (e.g. it.contacts.email) and ExoQuery will translate this to the correct JSON operator for your database:

Postgres/SQLite: -> for objects and ->> for text values
MySQL: JSON_EXTRACT(..., '$.path') and JSON_UNQUOTE(...)
SQL Server: JSON_QUERY(..., '$.path') for objects and JSON_VALUE(..., '$.path') for scalar values

This works recursively for nested JSON objects as well.

Example: Single-Level JSON Extraction

Suppose you have a contacts JSON column with ContactInfo(email: String, phone: String) and @SqlJsonValue is applied to ContactInfo:

@SqlJsonValue
@Serializable
data class ContactInfo(val email: String, val phone: String)

@Serializable
data class User(val id: Int, val name: String, val contacts: ContactInfo)

val emails = sql {
  Table<User>().map { it.contacts.email }
}.buildFor.Postgres().runOn(ctx)
// Postgres: SELECT contacts ->> 'email' AS value FROM Users
// SQLite:   SELECT contacts ->> 'email' AS value FROM Users
// MySQL:    SELECT JSON_UNQUOTE(JSON_EXTRACT(contacts, '$.email')) AS value FROM Users
// SQLSrv:   SELECT JSON_VALUE(contacts, '$.email') AS value FROM Users

You can also filter by JSON fields using the same property syntax:

val q = sql { Table<User>().filter { it.contacts.phone == "555-1234" } }
// Postgres/SQLite: WHERE contacts ->> 'phone' = '555-1234'
// MySQL:           WHERE JSON_UNQUOTE(JSON_EXTRACT(contacts, '$.phone')) = '555-1234'
// SQL Server:      WHERE JSON_VALUE(contacts, '$.phone') = '555-1234'

Example: Nested JSON Extraction

For nested JSON structures where ShippingInfo contains an Address JSON object:

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

@SqlJsonValue
@Serializable
data class ShippingInfo(val carrier: String, val address: Address)

@Serializable
data class Order(val id: Int, val amount: Double, val shipping: ShippingInfo)

val cities = sql {
  Table<Order>().map { it.shipping.address.city }
}.buildFor.Postgres().runOn(ctx)
// Postgres: SELECT shipping -> 'address' ->> 'city' AS value FROM Orders
// SQLite:   SELECT shipping -> 'address' ->> 'city' AS value FROM Orders
// MySQL:    SELECT JSON_UNQUOTE(JSON_EXTRACT(JSON_EXTRACT(shipping, '$.address'), '$.city')) AS value FROM Orders
// SQLSrv:   SELECT JSON_VALUE(JSON_QUERY(shipping, '$.address'), '$.city') AS value FROM Orders

val canadianOrders = sql {
  Table<Order>().filter { it.shipping.address.country == "CA" }
}
// Postgres/SQLite: WHERE shipping -> 'address' ->> 'country' = 'CA'
// MySQL:           WHERE JSON_UNQUOTE(JSON_EXTRACT(JSON_EXTRACT(shipping, '$.address'), '$.country')) = 'CA'
// SQL Server:      WHERE JSON_VALUE(JSON_QUERY(shipping, '$.address'), '$.country') = 'CA'

Explicit JSON Field Extraction

In addition to implicit extraction via Kotlin property access, you can explicitly extract JSON fields using the json DSL:

json.extract(column, fieldName): returns the JSON node/object at the given field path
json.extractAsText(column, fieldName): returns the scalar value of the field as text

Explicit extraction is useful when you want to be explicit about JSON operations, need to compose nested extractions, or are working in places where property access is less convenient.

Example: Single-Level Explicit Extraction

@SqlJsonValue
@Serializable
data class ContactInfo(val email: String, val phone: String)

@Serializable
data class User(val id: Int, val name: String, val contacts: ContactInfo)

val emails = sql {
  Table<User>().map { json.extractAsText(it.contacts, "email") }
}.buildFor.Postgres().runOn(ctx)
// Postgres: SELECT contacts ->> 'email' AS value FROM Users
// SQLite:   SELECT contacts ->> 'email' AS value FROM Users
// MySQL:    SELECT JSON_UNQUOTE(JSON_EXTRACT(contacts, '$.email')) AS value FROM Users
// SQLSrv:   SELECT JSON_VALUE(contacts, '$.email') AS value FROM Users

You can also filter by JSON fields using explicit extraction:

val q = sql { Table<User>().filter { json.extractAsText(it.contacts, "phone") == "555-1234" } }
// Postgres/SQLite: WHERE contacts ->> 'phone' = '555-1234'
// MySQL:           WHERE JSON_UNQUOTE(JSON_EXTRACT(contacts, '$.phone')) = '555-1234'
// SQL Server:      WHERE JSON_VALUE(contacts, '$.phone') = '555-1234'

Example: Nested Explicit Extraction

For nested JSON structures, compose json.extract and json.extractAsText:

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

@SqlJsonValue
@Serializable
data class ShippingInfo(val carrier: String, val address: Address)

@Serializable
data class Order(val id: Int, val amount: Double, val shipping: ShippingInfo)

val cities = sql {
  Table<Order>().map { ex -> json.extractAsText(json.extract(ex.shipping, "address"), "city") }
}.buildFor.Postgres().runOn(ctx)
// Postgres: SELECT shipping -> 'address' ->> 'city' AS value FROM Orders
// SQLite:   SELECT shipping -> 'address' ->> 'city' AS value FROM Orders
// MySQL:    SELECT JSON_UNQUOTE(JSON_EXTRACT(JSON_EXTRACT(shipping, '$.address'), '$.city')) AS value FROM Orders
// SQLSrv:   SELECT JSON_VALUE(JSON_QUERY(shipping, '$.address'), '$.city') AS value FROM Orders

val canadianOrders = sql {
  Table<Order>().filter { json.extractAsText(json.extract(it.shipping, "address"), "country") == "CA" }
}
// Postgres/SQLite: WHERE shipping -> 'address' ->> 'country' = 'CA'
// MySQL:           WHERE JSON_UNQUOTE(JSON_EXTRACT(JSON_EXTRACT(shipping, '$.address'), '$.country')) = 'CA'
// SQL Server:      WHERE JSON_VALUE(JSON_QUERY(shipping, '$.address'), '$.country') = 'CA'

These explicit JSON operations provide fine-grained control and can be composed for arbitrarily deep paths while maintaining clear intent.

Inserting JSON Data

Use param() to insert JSON data just like any other value:

val newContacts = ContactInfo("bob@example.com", "555-5678")
val q = sql {
  insert<User> {
    set(id to 2, name to "Bob", contacts to param(newContacts))
  }
}
q.build<PostgresDialect>().runOn(ctx)
//> INSERT INTO Users (id, name, contacts) VALUES (?, ?, ?)

In this situation, ExoQuery automatically delegates param(newContacts) to the paramCustom method.

The ContactInfo object will be automatically serialized to JSON format when inserted into the database.

Inserting Complete Rows

You can insert entire rows using setParams():

val user = User(2, "Bob", ContactInfo("bob@example.com", "555-5678"))
val q = sql {
  insert<User> { setParams(user).excluding(id) }
}