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.eq
import org.jetbrains.exposed.v1.jdbc.update

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 Teams 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 will 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:

        val projectText = TasksTable.project.function("JSON").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 you want to use SQLite with JSONB via the DAO approach, some adjustments will be required on the entity in order to make sure that the read value is always in a text format:

                object TasksTable : IntIdTable("tasks") {
                    // ...
                    val project = jsonb<Project>("project", Json.Default)
                    val projectAsJson = CustomFunction("JSON", project.columnType, project)
                }

                class TaskEntity(id: EntityID<Int>) : IntEntity(id) {
                    private var projectAsBinary by TasksTable.project

                    var project: Project
                        get() = readValues.getOrNull(TasksTable.projectAsJson) ?: projectAsBinary
                        set(value) { projectAsBinary = value }

                    companion object : IntEntityClass<TaskEntity>(TasksTable) {
                        override fun searchQuery(op: Op<Boolean>): Query {
                            return super.searchQuery(op).adjustSelectedProject()
                        }

                        override fun all(): SizedIterable<TaskEntity> {
                            return wrapRows(TasksTable.selectAll().notForUpdate().adjustSelectedProject())
                        }

                        private fun Query.adjustSelectedProject(): Query = adjustSelect { original ->
                            select(original.fields - TasksTable.project + TasksTable.projectAsJson)
                        }
                    }
                }
            

In the example above, the traditional entity property delegate is made private while a custom project field is given a custom getter that always attempts to read the result of the custom JSON() from a query. This read logic falls back to the standard delegate result in the event the entity is being read directly from the cache.

The low-level methods that may be invoked by queries like TaskEntity.all() are then given overrides that always replace the original column with the function expression. With this implementation, a TaskEntity can be created, updated, and queried as normal and any JSON value set to its project field will be stored in the binary format and retrieved in text format.

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

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

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

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: 27 November 2025