JSON columns without the fuss

Some data is document-shaped: preferences, addresses, denormalized snapshots. Mark the field @Json and Storm serializes it into a JSON column on write and back into the typed field on read.

01The task

Store user preferences and a structured address without inventing tables for them, and load a user's roles without a second query. Relational where it matters, documents where it helps.

02Maps and collections

Add the JSON module for your serializer (storm-kotlinx-serialization, or storm-jackson2/storm-jackson3); Storm auto-detects it at runtime. Then annotate the field:

// @Json maps a field onto a JSON column
data class User(
    @PK val id: Int = 0,
    val email: String,
    @Json val preferences: Map<String, String>,
) : Entity<Int>

// Reads and writes serialize transparently
val user = orm insert User(
    email = "alice@example.com",
    preferences = mapOf("theme" to "dark", "locale" to "en"),
)

03Structured objects

JSON fields are not limited to maps. A domain object with a well-defined shape but no need for its own table stores directly, keeping its type on both ends:

// Structured objects work too: shape without a table
@Serializable   // for kotlinx.serialization; Jackson needs no annotation
data class Address(
    val street: String,
    val city: String,
    val postalCode: String,
)

data class User(
    @PK val id: Int = 0,
    val email: String,
    @Json val address: Address,
) : Entity<Int>

04One-to-many in one query

The same machinery powers a technique worth knowing: aggregate related rows into a JSON array inside the query, and Storm deserializes them into a typed list. A one-to-many loads in a single round trip, no N+1, no second query:

// JSON aggregation: a one-to-many loaded in a single query
data class RolesByUser(
    val user: User,
    @Json val roles: List<Role>,
)

interface UserRepository : EntityRepository<User, Int> {

    fun getUserRoles(): List<RolesByUser> =
        select<RolesByUser, _, _> { "${User::class}, JSON_OBJECTAGG(${Role::class})" }
            .innerJoin<UserRole>().on<User>()
            .groupBy(User_.id)
            .resultList
}

-- the related rows aggregate into a JSON array, one round trip
SELECT u.id, u.email, JSON_OBJECTAGG(r.id, r.name)
FROM "user" u
INNER JOIN user_role ur ON ur.user_id = u.id
INNER JOIN role r ON ur.role_id = r.id
GROUP BY u.id

This shines when the aggregated collection is moderate in size; for very large collections, splitting into two queries is cheaper than shipping megabytes of JSON. The JSON docs cover the performance envelope and when to split.

05Keep going

The reference documentation covers the mechanics in depth: