Tutorials/JSON columns

JSON columns in relational entities

Some data is document-shaped: preferences, diets, snapshots. Mark the field @Json and Storm writes it to a JSON column and reads it back into the typed field.

Series · The Storm way4 min readKotlin

01The task

Store owner preferences and a pet's diet without a table for each, and load the pet names of every owner in one query.

02Maps and collections

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

Owner.kt Kotlin · Storm
1
2
3
4
5
6
7
8
9
10
11
12
13
14
// @Json maps a field onto a JSON column
data class Owner(
    @PK val id: Int = 0,
    val firstName: String,
    val lastName: String,
    @Json val preferences: Map<String, String>,
) : Entity<Int>

// Written as JSON, read back as a Map
val owner = orm insert Owner(
    firstName = "Betty",
    lastName = "Davis",
    preferences = mapOf("reminders" to "email", "locale" to "en"),
)

With kotlinx.serialization and Jackson both on the classpath, the converter is chosen per field: kotlinx.serialization converts a type it has a serializer for, Jackson the rest. Classpath order plays no part.

03Structured objects

A JSON field can also hold a domain object with a fixed shape that does not need a table of its own. It keeps its type on both ends:

Pet.kt Kotlin · Storm
1
2
3
4
5
6
7
8
9
10
11
12
13
14
// A structured object with a fixed shape and no table of its own
@Serializable   // for kotlinx.serialization; Jackson needs no annotation
data class Diet(
    val food: String,
    val gramsPerDay: Int,
    val allergies: List<String>,
)

data class Pet(
    @PK val id: Int = 0,
    val name: String,
    @FK val owner: Owner,
    @Json val diet: Diet,
) : Entity<Int>

Reading ignores a property the class does not declare. @Json(failOnUnknown = true) refuses such a document instead.

04One-to-many in one query

The same conversion reads the result of a JSON aggregate. The database collects the related rows of each group into a JSON array, and Storm reads the array into a typed list, so the one-to-many loads in one round trip:

OwnerRepository.kt Kotlin · Storm
1
2
3
4
5
6
7
8
9
10
11
12
13
14
// The pet names of each owner, aggregated into a JSON array by the database
data class PetNamesByOwner(
    val owner: Owner,
    @Json val pets: List<String>,
)

interface OwnerRepository : EntityRepository<Owner, Int> {

    fun getPetNames(): List<PetNamesByOwner> =
        select(PetNamesByOwner::class) { "${Owner::class}, JSON_ARRAYAGG(${Pet_.name})" }
            .innerJoin<Pet>().on<Owner>()
            .groupBy(Owner_.id)
            .resultList
}
↳ generated sql
-- the pet names of each owner arrive as one JSON array column
SELECT o.id, o.first_name, o.last_name, o.preferences, JSON_ARRAYAGG(p.name)
FROM owner o
INNER JOIN pet p ON p.owner_id = o.id
GROUP BY o.id

This fits a collection of moderate size that the caller uses whole. When a collection can grow without bound, or has to be paged, read it with a second query instead: orm.entity(Pet::class).select().where(Pet_.owner inList owners).resultGroupedBy(Pet_.owner) returns each owner with their pets as a map, grouped while Storm reads the rows. The JSON docs give the limits and when to split.

05Keep going

The reference documentation covers the mechanics in depth: