JSON and JSONB types

Exposed works together with the JSON serialization library of your choice by allowing column definitions that accept generic serializer and deserializer arguments through the json() and jsonb() functions.

Databases store JSON values in either text or binary format, so Exposed provides a separate type for each.

Add dependencies

Before using JSON and JSONB column types or functions, add the exposed-json module to your build file:

                dependencies {
                    implementation("org.jetbrains.exposed:exposed-json:1.4.0")
                }
                

                <dependencies>
                    <dependency>
                        <groupId>org.jetbrains.exposed</groupId>
                        <artifactId>exposed-json</artifactId>
                        <version>1.4.0</version>
                    </dependency>
                </dependencies>
                

                dependencies {
                    implementation "org.jetbrains.exposed:exposed-json:1.4.0"
                }
                

Basic usage

The following example uses kotlinx.serialization with a @Serializable class. This overload of json() accepts a Json configuration and uses the KSerializer for the specified type:

import kotlinx.serialization.Serializable
import kotlinx.serialization.json.Json
import org.jetbrains.exposed.v1.core.Table
import org.jetbrains.exposed.v1.json.json

@Serializable
data class Project(val name: String, val language: String, val active: Boolean)

val format = Json { prettyPrint = true }

object TeamsTable : Table("team") {
    val groupId = varchar("group_id", 32)

    // Equivalent to json("project", format, Project.serializer()).
    val project = json<Project>("project", format)
}

You can also provide serializer and deserializer functions directly. For example, the following definition uses Jackson with the jackson-module-kotlin dependency and the full form of json():

import com.fasterxml.jackson.module.kotlin.jacksonObjectMapper
import com.fasterxml.jackson.module.kotlin.readValue
import org.jetbrains.exposed.v1.core.Table
import org.jetbrains.exposed.v1.json.json

val mapper = jacksonObjectMapper()

object JacksonTeamsTable : Table("team") {
    val groupId = varchar("group_id", 32)
    val project = json(
        "project",
        { mapper.writeValueAsString(it) },
        { mapper.readValue<Project>(it) }
    )
}

Insert and update JSON data

The following examples use the TeamsTable definition from the kotlinx.serialization example.

To store a JSON value, assign an instance of the serializable class to the column. Exposed serializes the value using the Json instance passed to the jsonConfig parameter of json():

        val mainProject = Project("Main", "Java", true)
        TeamsTable.insert {
            it[groupId] = "A"
            it[project] = mainProject
        }

To modify a stored value, assign a new instance in an update() statement:

        TeamsTable.update({ TeamsTable.groupId eq "A" }) {
            it[project] = mainProject.copy(language = "Kotlin")
        }

When you read the column, Exposed deserializes the stored JSON back into an instance of the class:

        TeamsTable
            .selectAll()
            .map { "Team ${it[TeamsTable.groupId]} -> ${it[TeamsTable.project]}" }
            .forEach { println(it) }
        // Team A -> Project(name=Main, language=Kotlin, active=true)

Store arrays

JSON columns can also store arrays. Pass the corresponding Kotlin array type to json(), for example IntArray for integers or Array<Project> for objects:

object TeamProjectsTable : Table("team_projects") {
    val memberIds = json<IntArray>("member_ids", Json.Default)
    val projects = json<Array<Project>>("projects", Json.Default)
    // Equivalent to:
    // @OptIn(ExperimentalSerializationApi::class) json("projects", Json.Default, ArraySerializer(Project.serializer()))
}

To insert values into these columns, use standard Kotlin collections:

        TeamProjectsTable.insert {
            it[memberIds] = intArrayOf(1, 2, 3)
            it[projects] = arrayOf(
                Project("A", "Kotlin", true),
                Project("B", "Java", true)
            )
        }

    INSERT INTO team_projects (member_ids, projects)
    VALUES ([1,2,3], [{"name":"A","language":"Kotlin","active":true},{"name":"B","language":"Java","active":true}])

Supported types

The exposed-json module provides the following column types:

Column type	PostgreSQL	MySQL / MariaDB / H2	SQLite	SQLServer	Oracle
`json()`	`JSON`	`JSON`	`TEXT`	`NVARCHAR(MAX)`	`VARCHAR2(4000)`
`jsonb()`	`JSONB`	`JSON`	`BLOB`	Not supported	Not supported

The exact SQL type depends on the database dialect. For example, jsonb() maps to JSON in MySQL and H2 rather than to a type named JSONB.

`json()`

Use json() to define a column that stores JSON data in a text-based representation.

When using kotlinx.serialization, pass the Json instance to the jsonConfig parameter:

val project = json<Project>("project", jsonConfig = format)

`jsonb()`

Use the jsonb() to define a column for JSON data that the database can store in a binary representation, where supported.

When using kotlinx.serialization, pass the Json instance to the jsonConfig parameter:

val project = jsonb<Project>("project", jsonConfig = Json.Default)

JSONB support in SQLite

SQLite supports storing JSON data in its binary JSONB format starting with version 3.45.0.0. Exposed maps jsonb() columns to BLOB and wraps values written to them with SQLite's JSONB() function.

This applies to values in DDL default clauses:

object TasksTable : Table("tasks") {
    val complete = bool("complete").default(false)
    val project = jsonb<Project>("project", Json.Default)
        .default(Project("Main", "Kotlin", true))
}

CREATE TABLE IF NOT EXISTS tasks (
    complete BOOLEAN DEFAULT 0 NOT NULL,
    project BLOB DEFAULT (JSONB('{"name":"Main","language":"Kotlin","active":true}')) NOT NULL
)

Exposed also wraps values in JSONB() in DML operations:

        TasksTable.insert {
            it[project] = Project("Main", "Java", true)
        }

    INSERT INTO tasks (project)
    VALUES (JSONB('{"name":"Main","language":"Java","active":true}'))

SQLite stores this value in its binary JSONB representation. A serializer that expects JSON text cannot decode the raw stored value directly.

To make the value available as JSON text, SQLite provides the JSON() SQL function. By default, Exposed applies this function when it reads a jsonb() column from SQLite.

        val projectText = TasksTable.project.alias("ptext")
        val projects = TasksTable.select(projectText).map { it[projectText] }

SELECT JSON(tasks.project) ptext FROM tasks

To disable this behavior, set the castToJsonFormat parameter to false when you define the column:

object TasksRawTable : Table("tasks_raw") {
    val project = jsonb<Project>("project", Json.Default, castToJsonFormat = false)
}

Exposed ignores castToJsonFormat for databases other than SQLite. To convert an individual JSONB expression to JSON, use .castToJson().

JSON functions

Extract data

Use the .extract() function to extract a value from a JSON expression at a specific path. You can extract the result as JSON or as a scalar value of the specified type.

For example, the following query extracts the project name and selects projects whose language is Kotlin:

        val projectName = TeamsTable.project.extract<String>(".name")
        val languageIsKotlin =
            TeamsTable.project.extract<String>(".language").lowerCase() eq "kotlin"
        TeamsTable
            .select(projectName)
            .where { languageIsKotlin }
            .map { it[projectName] }

For databases that use $ as the JSON path root, Exposed adds it to the generated path expression automatically, so don't include $ in the path you pass to .extract(). For example, in MySQL, pass .name instead of $.name.

Check if data exists

To check whether data exists within a JSON expression, use the .exists() function:

        val hasActiveStatus = TeamsTable.project.exists(".active")
        val activeProjects = TeamsTable.selectAll().where { hasActiveStatus }.count()

Some databases also support filter expressions and optional variables in JSON paths:

        val mainId = "Main"
        val hasMainProject = TeamsTable.project.exists(
            ".name ? (@ == \\$main)",
            optional = "{\"main\":\"$mainId\"}"
        )
        val mainProjects = TeamsTable
            .selectAll()
            .where { hasMainProject }
            .map { it[TeamsTable.groupId] }

Check if JSON contains an expression

To check whether a JSON expression contains a value, use the .contains() function:

        val usesKotlin = TeamsTable.project.contains("{\"language\":\"Kotlin\"}")
        val kotlinTeams = TeamsTable.selectAll().where { usesKotlin }.count()

On supported databases, you can also limit the check to a specific JSON path:

        val usesKotlinWithPath = TeamsTable.project.contains(
            "\"Kotlin\"",
            ".language"
        )
        val kotlinTeams = TeamsTable
            .selectAll()
            .where { usesKotlinWithPath }
            .count()

Cast data to JSON type

Use the .castToJson() function to cast other supported types, such as JSONB, to JSON:

        JsonCastTable.select(
            // Assumes this column is a JSONB column of type <Project>.
            JsonCastTable.project.castToJson(),
            // Assumes this column stores valid JSON string input like
            // "{"name":"Main","language":"Java","active":true}".
            JsonCastTable.projectText.castToJson<Project>()
        ).toList()

As shown on the example above, on supported databases, you can also cast a text column that stores valid JSON strings to a serializable class.

18 August 2026