← Back to Blog
• By ExoQuery Team

ExoQuery 2.0: JSON Column extraction for the price of a dot

Query into JSON columns across Postgres, SQLite, MySQL, and SQL Server by writing plain Kotlin property access. No vendor lock-in, no dialect if/elses.

featurejsoncross-platform

TL;DR

  • Using ExoQuery 2.0, you can query into JSON columns across Postgres, SQLite, MySQL, and SQL Server by writing plain Kotlin property access like it.shipping.address.city.
  • ExoQuery figures out the right JSON operators per database automatically. I pay the “price” of simple field access, and get portable JSON SQL for free.
  • Works for nested JSON objects, selection, filtering, sorting, and projections. No vendor lock-in, no dialect if/elses.

The pain this removes

If you’ve ever worked with JSON columns across multiple databases, you know the drill:

  • Postgres/SQLite want ->/->>.
  • MySQL wants JSON_EXTRACT(...) plus JSON_UNQUOTE(...) for scalars and you need to remember to add $. before everything.*
  • SQL Server wants JSON_QUERY vs JSON_VALUE depending on object vs scalar.
  • And then you have to remember where to cast and how to nest paths.

Multiply that by “we might switch databases later” and it becomes a labyrinth of stringy SQL with lots of tests to keep it from breaking.

What ExoQuery does instead

ExoQuery just lets you write the property you mean. If a column is a Kotlin @SqlJsonValue type, you can navigate into it like normal data:

One Level

import io.exoquery.*
import io.exoquery.controller.SqlJsonValue
import kotlinx.serialization.Serializable

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

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

//sampleStart
val emails = sql {
    Table<User>().map { it.contacts.email }
}
// Postgres:   SELECT contacts ->> 'email'         FROM ...
// SQL Server: SELECT JSON_VALUE(contacts, '$.email') FROM ...
//sampleEnd

suspend fun main() = emails.buildPrettyFor.Postgres().runSample()

Two levels

import io.exoquery.*
import io.exoquery.controller.SqlJsonValue
import kotlinx.serialization.Serializable

@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)

//sampleStart
val cities = sql {
    Table<Order>().map { it.shipping.address.city }
}
// Postgres:   SELECT shipping -> 'address' ->> 'city'                     FROM ...
// SQL Server: SELECT JSON_VALUE(JSON_QUERY(shipping, '$.address'), '$.city') FROM ...
//sampleEnd

suspend fun main() = cities.buildPrettyFor.Postgres().runSample()

Filters

import io.exoquery.*
import io.exoquery.controller.SqlJsonValue
import kotlinx.serialization.Serializable

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

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

//sampleStart
val usersWithPhone = sql {
    Table<User>().filter { it.contacts.phone == "555-1234" }
}
// Postgres:    ...WHERE contacts ->> 'phone' = '555-1234'
// SQL Server:  ...WHERE JSON_VALUE(contacts, '$.phone') = '555-1234'
//sampleEnd

suspend fun main() = usersWithPhone.buildPrettyFor.Postgres().runSample()
import io.exoquery.*
import io.exoquery.controller.SqlJsonValue
import kotlinx.serialization.Serializable

@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)

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

suspend fun main() = canadianOrders.buildPrettyFor.Postgres().runSample()

ExoQuery then generates the correct SQL JSON extraction for your target dialect:

  • Postgres/SQLite: -> for objects, ->> for scalars (plus casting where needed)
  • MySQL: JSON_EXTRACT and JSON_UNQUOTE
  • SQL Server: JSON_QUERY (objects) vs JSON_VALUE (scalars)

No special syntax. No vendor conditionals. No handwritten JSON operators. Just… dot access.

Why I’m excited

  • Portability: The same Kotlin code works across Postgres, SQLite, MySQL, and SQL Server.
  • Nesting that scales: Deeply nested JSON is still a one-liner from my perspective.
  • Safety: I get typed models, and ExoQuery picks the right operator/casting. Fewer footguns.
  • Focus: I think in terms of data shapes, not operator trivia.

Concrete examples

Say I have:

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

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

Now selecting an email is simply:

import io.exoquery.*
import io.exoquery.controller.SqlJsonValue
import kotlinx.serialization.Serializable

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

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

//sampleStart
val emails = sql { Table<User>().map { it.contacts.email } }
// Postgres: 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
//sampleEnd

suspend fun main() = emails.buildPrettyFor.Postgres().runSample()
  • Postgres/SQLite become: SELECT contacts ->> 'email' ...
  • MySQL becomes: SELECT JSON_UNQUOTE(JSON_EXTRACT(contacts, '$.email')) ...
  • SQL Server becomes: SELECT JSON_VALUE(contacts, '$.email') ...

Nested JSON? Same vibe:

import io.exoquery.*
import io.exoquery.controller.SqlJsonValue
import kotlinx.serialization.Serializable

@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)

//sampleStart
// Map cities
val mapCities = sql { Table<Order>().map { it.shipping.address.city } }
// Postgres: 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
//sampleEnd

suspend fun main() = mapCities.buildPrettyFor.Postgres().runSample()
import io.exoquery.*
import io.exoquery.controller.SqlJsonValue
import kotlinx.serialization.Serializable

@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)

//sampleStart
// Filter by nested country
val filterByCountry = sql { Table<Order>().filter { it.shipping.address.country == "CA" } }
// Postgres/SQLite: SELECT id, amount, shipping FROM Orders WHERE shipping -> 'address' ->> 'country' = 'CA'
// MySQL:           SELECT id, amount, shipping FROM Orders WHERE JSON_UNQUOTE(JSON_EXTRACT(JSON_EXTRACT(shipping, '$.address'), '$.country')) = 'CA'
// SQL Server:      SELECT id, amount, shipping FROM Orders WHERE JSON_VALUE(JSON_QUERY(shipping, '$.address'), '$.country') = 'CA'
//sampleEnd

suspend fun main() = filterByCountry.buildPrettyFor.Postgres().runSample()

Under the hood it emits the right JSON pathing per dialect (including the JSON_QUERY → JSON_VALUE handoff in SQL Server and the double JSON_EXTRACT hop in MySQL for nested objects).

What about performance and casting?

  • ExoQuery uses the idiomatic operator/functions for each DB, the same ones you’d hand-write.
  • Numeric comparisons will cast appropriately per dialect (e.g., (->> 'age')::INTEGER on Postgres), so comparisons stay correct.
  • If you want to index, you can still create expression indexes or generated columns as you normally would. ExoQuery won’t block you from doing the right thing for your database.

What else can I do?

In addition to projecting, you can also:

  • Filter: filter { it.contacts.phone.startsWith("555") } (ExoQuery will translate operations it knows)
  • Sort: orderBy(it.shipping.address.country)
  • Combine with operations on normal columns: map { it.id to it.contacts.email }
  • Use JSON-projected columns to join to other tables! (Try Exercise 3 from this interactive code sample).

Limitations

  • Your JSON-holding Kotlin types should be annotated with @SqlJsonValue so ExoQuery knows to apply JSON semantics.
  • Deeply nested paths are fine, but if you’re doing heavy querying on the same path, consider DB-side indexes/generator columns for speed.

It’s so Good it feels like cheating

ExoQuery implicit JSON extraction collapses a historically gnarly, vendor-specific surface area into “write the field you want.” The cost is just the “price of field access,” and the payoff is portable JSON SQL that reads like your domain.

If you want to try it

  • Try this feature out on the ExoQuery Kotlin Playground: Json Field Projection
  • Click the “Download as Gradle Project” button to get started with a sample project you can run locally in IntelliJ!

Comments