JSON and JSONB

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.

As databases store JSON values either in text or binary format, Exposed provides two types to account for any potential differences, if they exist.

Basic usage

The following example leverages kotlinx.serialization to support @Serializable classes. It uses a simpler form of json() that relies on the library's KSerializer interface:

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

const val GROUP_ID_LENGTH = 32

@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", GROUP_ID_LENGTH)
    val project = json<Project>("project", format) // equivalent to json("project", format, Project.serializer())
}

Here's how the same Project and TeamsTable would be defined using 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

const val GROUP_ID_LENGTH = 32

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

val mapper = jacksonObjectMapper()

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

Supported types

The exposed-json extension provides the following additional types:

json()

Maps to database JSON. Used for storing JSON data in text format.

Database-specific mappings:

SQLite: Maps to TEXT
SQLServer: Maps to NVARCHAR(MAX)
Oracle: Maps to VARCHAR2(4000)

jsonb()

Maps to database JSONB. Used for storing JSON data in binary format.

Database-specific mappings:

MySQL and H2: Maps to JSON
SQLite: Maps to BLOB from version 3.45.0.0+; otherwise, not supported
Oracle: Not supported

SQLite and JSONB

Storing JSON strings as JSONB binary format is supported in SQLite starting from version 3.45.0.0. To facilitate this, any JSON value set to a .jsonb() column will be automatically wrapped by the SQL function JSONB(). This applies to values set in DDL default clauses:

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

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
 )

And to values set in DML operations:

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

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

This means that attempting to read the value directly as it is stored in the column would throw a decode exception due to the non-readable JSONB binary format. SQLite provides the SQL function JSON() when it is required to read the stored value in a text format, which Exposed uses by default whenever the column is included in a SELECT clause:

        val projectText = TasksTable.project.alias("ptext")
        val projects = TasksTable.select(projectText).map { it[projectText] }
        println(projects) // [Project(name=Main, language=Java, active=true)]

SELECT JSON(tasks.project) ptext FROM tasks

If this automatic function wrapping in queries is not wanted, it can be toggled off by setting the parameter castToJsonFormat to false. This parameter is ignored if any other databases are detected.

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

The JSON() function can also be applied to individual JSONB columns by using .castToJson().

JSON functions

Extract data

You can use JSON path strings to extract values as JSON or as a scalar value at a specific field/key. To do this, use the .extract() function:

        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] }

Databases that support a path context root $ will have this value appended to the generated SQL path expression by default, so it is not necessary to include it in the provided argument string. In the above example, if MySQL is being used, the provided path arguments should be .name and .language respectively.

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

Depending on the database, filter paths can be provided instead, as well as optional arguments:

        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 an expression is contained within a JSON, use the .contains() function:

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

Depending on the database, you could also provide an optional path:

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

Cast data to JSON type

You can cast other supported data types, like text or JSONB, to the JSON data type using the .castToJson() function:

        ProjectsTable.select(
            // assumes this column is a JSONB column of type <Project>
            ProjectsTable.projects.castToJson(),
            // assumes this column stores valid string input like "{"group":"A","id":"a"}"
            ProjectsTable.groupId.castToJson<GroupId>()
        ).toList()

If supported by your database, this function can be used to cast valid JSON strings stored in a text column to the serializable class of your choosing.

JSON arrays

JSON columns also accept JSON arrays as input values, allowing structured data to be stored and manipulated directly in the database.

To define a JSON column that holds an array, use the json() function with the appropriate type. The following example defines JSON columns for arrays of primitive values and objects using the serializable data class Project:

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

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 data into the JSON array columns, use standard Kotlin collections:

        TeamProjectsTable.insert {
            it[memberIds] = intArrayOf(INT_ARRAY_ITEM_1, INT_ARRAY_ITEM_2, INT_ARRAY_ITEM_3)
            it[projects] = arrayOf(
                Project("A", "Kotlin", true),
                Project("B", "Java", true)
            )
        }

This results in the following SQL query:

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

Last modified: 22 January 2026