Json(b) data type support

[Tapac]

Databases which support json(b) columns:

1. Mysql 5.7+
2. PostgreSQL
3. Oracle

[quangIO]

I wrote an extension for PostgreSQL, which supports basic read and write operations in case someone needs it: jsonb.kt

[boonshift]

I added on a little bit on what @quangIO did... hopefully something like this or better will be supported by Exposed. jsonb.kt, main.kt and db.kt

[alacoste]

If anyone is running into issues with a ClassCastException to PGobject, I had the issue as well, see: https://gist.github.com/quangIO/a623b5caa53c703e252d858f7a806919#gistcomment-2902133

Completely beginner in this framework, so please correct me if I'm terribly wrong!

[AlexMiroshnikov]

@Tapac Any updates on the topic? Plans/roadmaps/merge requests/anything? 😊

[Tapac]

@AlexMiroshnikov, I will add it to a roadmap, but I want to escape using stong dependency to jackson or another json parsing library, so have to think about how to implement it properly.

[AlexanderTar]

@AlexMiroshnikov, I will add it to a roadmap, but I want to escape using stong dependency to jackson or another json parsing library, so have to think about how to implement it properly.

@Tapac May I suggest using semi-official kotlinx.serialization library?

[Rubyj]

@quangIO @boonshift Seems like these answers are a little out of date for the most current version of Kotlin. Does anyone have any updated code?\

The introduction of PreparedStatementApi changed this code.

[yat90]

@Rubyj I made some changes to the Code of @boonshift to get it work for me, see jsonb.kt

[Rubyj]

@yat90 This is great. Thanks! I had to make a few edits so it would pass ktlint. You can find those included jsonb.kt

[Rubyj]

@yat90 How is your column used? I am unable to do something like

object Schema : Table(name = "schema") {
    val id = varchar("id", 50)
    override val primaryKey: PrimaryKey = PrimaryKey(id)
    val definition = jsonb("definition") //Type inference failed
}

I ended up with something like val definition = jsonb("definition", Map::class.java, Gson(), false)

I insert with

SchemaOptions.insert {
    it[id] = "test-schema-id"
    it[definition] = mapOf("test" to "test")
}

and I receive the following error when testing (using H2 in memory db for that):

org.jetbrains.exposed.exceptions.ExposedSQLException: org.h2.jdbc.JdbcSQLDataException: Data conversion error converting "X'aced00057372001c6f72672e706f737467726573716c2e7574696c2e50476f626a656374f903de2682bdcb3b0200024c0004747970657400124c6a6176612f6c616e672f537472696e673b4c000576616c756571007e000178707400056a736f6e6274000f7b2274657374223a2274657374227d' (SCHEMA: ""DEFINITION"" JSON NOT NULL)"; SQL statement:

Is this the expected use? I believe my Postgres column is JSON not JSONB

If I change the SQL column type to JSONB I get the following error now because H2 does not understand jsonB:

org.h2.jdbc.JdbcSQLDataException: Hexadecimal string contains non-hex character

Is there a way to use json instead of jsonb and not save as binary?

[yat90]

@Rubyj Here is how I used it.

My Table Definition looks like

class Product(id: EntityID<UUID>) : UUIDEntity(id) {
    companion object : UUIDEntityClass<Product>(ProductTable)
    var key by ProductTable.key
    var defaultParams by ProductTable.defaultParams
}

object ProductTable : UUIDTable("product") {
    val key = varchar("key", 50)
    val defaultParams = jsonb("default_params", MutableMap::class.java, Gson(), true)
    override val primaryKey = PrimaryKey(id, name = "product_pk")
}

And I'm inserting like this

var paramsMap: MutableMap<String, Any> = HashMap<String, Any>()
paramsMap.put("foo", "bar)

transaction {
    Product.new {
        key = UUID.randomUUID().toString()
        defaultParams = paramsMap
    }
}

As you can see in the code of @boonshift he has some different functions for MariaDB Support and for the PostgreSQL Support in his code. One of the different is the SQL Type and the toSQL function.

I think you have to adjust the SQL Type and the toQueryBuilder function that it can work with H2 Databases.

[ib-jamesp]

@yat90, thanks for posting your code, could you utilize the same for persisting a json array to a jsonb field? I've been looking at how to persist and retrieve however seem to get type issues.

[nvta-sbiyyala]

@ib-jamesp I was able to persist a json array to jsonb, verified its queryable

On a separate note: I prefer using jackson over gson as the parsing library - Here's a jackson version of @yat90 's solution (Thank you!) - https://gist.github.com/nvta-sbiyyala/fa327d449d98f37dacead7241af2c683

[ib-jamesp]

Thanks @nvta-sbiyyala, I ended up adding support for passing an interface into the jsonb field so that we were not coupled to a specific JSON parser (as mentioned by Tapac here). We are now parsing the json field by type.

fun <T : Any> Table.jsonb(name: String, jsonParser: IJsonParser, nullable: Boolean): Column<T> {
    return registerColumn<T>(name, ParsedJsonColumnType<T>(jsonParser, nullable))
}

class ParsedJsonColumnType<out T : Any>(private val parser: IJsonParser, override var nullable: Boolean) : IColumnType {
    override fun setParameter(stmt: PreparedStatementApi, index: Int, value: Any?) {
        val obj = PGobject()
        obj.type = "jsonb"
        if (value != null)
            obj.value = parser.toJson(value)
        stmt[index] = obj
    }

    @Suppress("TooGenericExceptionCaught", "TooGenericExceptionThrown")
    override fun valueFromDB(value: Any): Any = when (value) {
        is HashMap<*, *> -> value
        is Map<*, *> -> value
        else -> {
            value as PGobject
            try {
                val json = value.value
                parser.fromJson(json)!! // this will throw null reference on purpose
            } catch (e: Exception) {
                e.printStackTrace()
                throw RuntimeException("Can't parse JSON: $value")
            }
        }
    }
    override fun notNullValueToDB(value: Any): Any = parser.toJson(value)
    override fun nonNullValueToString(value: Any): String = "'${parser.toJson(value)}'"
    override fun sqlType() = "jsonb"
}

// Interface
interface IJsonParser {
    fun fromJson(json: String): Any?
    fun toJson(source: Any): String
}

// Implementation of jsonb field with parser as 'MetaData' is abstract class with Type embedded
val classTypeMetadata = jsonb<MetaData>("meta_data", MetaDataParser(), true)

I would rather have the parsers abstracted away from our code in case we ever replace the implementation.

[nvta-sbiyyala]

Thanks @ib-jamesp ! Tested the Jackson implementation -

class JacksonParser<T>(
    private val klass: Class<T>,
    private val jsonMapper: ObjectMapper = jacksonObjectMapper()
) : IJsonParser {

    override fun fromJson(json: String): T = jsonMapper.readValue<T>(json, klass)

    override fun toJson(source: Any): String = jsonMapper.writeValueAsString(source)
}

[qoomon]

I've adjusted the other examples a little and add JsonParser Implementations for kotlinx.serialization, Gson and Jackson

package me.qoomon.examples

import com.fasterxml.jackson.databind.ObjectMapper
import com.google.gson.Gson
import kotlinx.serialization.KSerializer
import kotlinx.serialization.json.Json
import kotlinx.serialization.json.JsonConfiguration
import org.jetbrains.exposed.sql.statements.api.PreparedStatementApi
import org.postgresql.util.PGobject

import kotlinx.serialization.serializerByTypeToken
import org.jetbrains.exposed.sql.*
import org.jetbrains.exposed.sql.Function

class JsonColumnType<out T : Any>(
    private val klass: Class<T>,
    private val parser: JsonParser,
    override var nullable: Boolean
) : ColumnType() {
    override fun sqlType() = "jsonb"

    override fun setParameter(stmt: PreparedStatementApi, index: Int, value: Any?) {
        stmt[index] = PGobject().apply {
            this.type = sqlType()
            this.value = value as String?
        }
    }

    override fun valueFromDB(value: Any) = when (value) {
        is PGobject -> parser.parse(klass, value.value)
        is Map<*, *> -> value
        is HashMap<*, *> -> value
        else -> throw IllegalArgumentException("Unexpected value type ${value::class}")
    }

    @Suppress("UNCHECKED_CAST")
    override fun notNullValueToDB(value: Any) = parser.stringify(klass, value as T)

    @Suppress("UNCHECKED_CAST")
    override fun nonNullValueToString(value: Any) = "'${parser.stringify(klass, value as T)}'"
}

class JsonKey(val key: String) : Expression<String>() {
    init {
        if (!key.matches(Regex("\\w+"))) throw IllegalArgumentException("Only simple json key allowed. Found '$key'")
    }

    override fun toQueryBuilder(queryBuilder: QueryBuilder) = queryBuilder { append(key) }
}

class JsonValue<T>(
    val expression: Expression<*>,
    val jsonKey: JsonKey,
    override val columnType: IColumnType
) : Function<T>(columnType) {
    override fun toQueryBuilder(queryBuilder: QueryBuilder) = queryBuilder {
        append("CAST((${expression.toQueryBuilder(queryBuilder)} ->> '${jsonKey.key}') AS ${columnType.sqlType()})")
    }
}

fun <T : Any> Table.jsonb(name: String, klass: Class<T>, parser: JsonParser, nullable: Boolean): Column<T> =
    registerColumn(name, JsonColumnType(klass, parser, nullable))

inline fun <reified T> Column<Map<*, *>>.json(jsonKey: JsonKey): Function<T> {
    val columnType = when (T::class) {
        Int::class -> IntegerColumnType()
        String::class -> VarCharColumnType()
        Boolean::class -> BooleanColumnType()
        else -> throw java.lang.RuntimeException("Column type ${T::class} not supported for json field.")
    }

    return JsonValue(this, jsonKey, columnType)
}

interface JsonParser {
    fun <T> parse(klass: Class<T>, string: String): T
    fun <T> stringify(klass: Class<T>, value: T): String
}

class KotlinxJsonParser(val json: Json = Json(JsonConfiguration.Stable)) : JsonParser {
    override fun <T> parse(klass: Class<T>, string: String) = json.parse(klass.serializer(), string)
    override fun <T> stringify(klass: Class<T>, value: T) = json.stringify(klass.serializer(), value)

    private val serializerMap = HashMap<Class<*>, KSerializer<Any>>()

    @Suppress("UNCHECKED_CAST")
    private fun <T> Class<T>.serializer() = serializerMap.getOrPut(this, {
        serializerByTypeToken(this)
    }) as KSerializer<T>
}

class GsonJsonParser(val gson: Gson = Gson()) : JsonParser {
    override fun <T> parse(klass: Class<T>, string: String) = gson.fromJson(string, klass)!!
    override fun <T> stringify(klass: Class<T>, value: T) = gson.toJson(value, klass)!!
}

class JacksonJsonParser(val objectMapper: ObjectMapper = ObjectMapper()) : JsonParser {
    override fun <T> parse(klass: Class<T>, string: String) = objectMapper.readValue<T>(string, klass)!!
    override fun <T> stringify(klass: Class<T>, value: T) = objectMapper.writeValueAsString(value)!!
}

[brezinajn]

@qoomon The Class<T> based approach would be problematic if you wanted to serialize for example List of something, wouldn't it? Wouldn't this be a better pattern?

fun <T : Any> Table.json(name: String, kSerializer: KSerializer<T>, json: Json): Column<T> =
    this.json(name = name, stringify = { json.stringify(kSerializer, it) }, parse = { json.parse(kSerializer, it) })

fun <T : Any> Table.json(name: String, stringify: (T) -> String, parse: (String) -> T): Column<T> =
    registerColumn(name, JsonColumnType(stringify, parse))

class JsonColumnType<T : Any>(private val stringify: (T) -> String, private val parse: (String) -> T) : ColumnType() {
    override fun sqlType(): String = "json"
    override fun valueFromDB(value: Any) = parse(value as String)

    @Suppress("UNCHECKED_CAST")
    override fun notNullValueToDB(value: Any) = stringify(value as T)

    override fun valueToString(value: Any?): String = when (value) {
        is Iterable<*> -> notNullValueToDB(value)
        else -> super.valueToString(value)
    }
}

Also do we really need the JsonParser interface?

[qoomon]

@brezinajn I agree

[qoomon]

here is my latest version https://gist.github.com/qoomon/70bbbedc134fd2a149f1f2450667dc9d

it now supports json field access by json path

[ib-jamesp]

@brezinajn / @qoomon The only reason I included a JsonParser was to keep away from a specific implementation of a json serializing package (Gson vs Jackson vs add your one here). Awesome work though as there are definite positives to your implementations!

[toastyblast]

I'm making use of the solution made by @Rubyj from this comment, when I found it several weeks ago. It's been working great for retrieving a JSONB field from my PostgreSQL 11 container. However, today I needed to add a case where I insert data with Kotlin Exposed to the DB, not just selecting from it anymore. This table too has a JSONB field, but whenever I try to push data to it, I get the following error:

Exception in thread "DefaultDispatcher-worker-5" org.jetbrains.exposed.exceptions.ExposedSQLException: org.postgresql.util.PSQLException: ERROR: column "payload" is of type jsonb but expression is of type character varying
  Hint: You will need to rewrite or cast the expression.
  Position: 157
SQL: [INSERT INTO Revisions (acknowledged_at, created_at, gatling_run_id, latest_attempt_at, message_id, payload, status, topic_name) VALUES (?, ?, ?, ?, ?, ?, ?, ?)]
<Error stack...>

I understand the error, I think, but I'm not sure what's causing it. I saw a somewhat similar issue in this comment from @Rubyj and this comment from @yat90 but I can't seem to get mine to work. See the relevant code here:

revision_table.sql:

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE revisions (
    message_id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
    gatling_run_id uuid,
    created_at TIMESTAMPTZ NOT NULL,
    latest_attempt_at TIMESTAMPTZ NOT NULL,
    acknowledged_at TIMESTAMPTZ,
    topic_name VARCHAR(256) NOT NULL,
    payload JSONB NOT NULL,
    status SMALLINT DEFAULT 1
)

revision.kt:

object Revisions : Table() {
    val messageID: Column<UUID> = uuid("message_id").autoGenerate().primaryKey()
    val createdAt: Column<DateTime> = datetime("created_at")
    val latestAttemptAt: Column<DateTime> = datetime("latest_attempt_at")
    val acknowledgedAt: Column<DateTime?> = datetime("acknowledged_at").nullable()
    val gatlingRunID: Column<UUID?> = uuid("gatling_run_id").nullable()
    val topicName: Column<String> = varchar("topic_name", 256)
    val payload = jsonb("payload", Map::class.java, Gson(), false)
    val status: Column<Short> = short("status").default(1)
}

data class Revision(
    val messageID: UUID,
    val createdAt: DateTime,
    val latestAttemptAt: DateTime,
    val acknowledgedAt: DateTime?,
    val gatlingRunId: UUID?,
    val topicName: String,
    val payload: Map<*, *>,
    val status: Short = 1
)

RevisionService.kt (only the relevant function):

object RevisionService {
    suspend fun logNewRevision(sendDate: DateTime, topic: String, message: Map<*, *>, runID: UUID? = null): UUID = dbQueryGatId(runID) {
        Revisions.insert {
            it[createdAt] = sendDate
            it[latestAttemptAt] = sendDate
            it[topicName] = topic
            it[payload] = message
            it[status] = 1
            it[gatlingRunID] = runID
        }[Revisions.messageID]
    }

    // ...
}

The only class that uses the function, DataOutMsgHandler.kt:

class DataOutMsgHandler(
    topicName: String,
    parentApp: String
) : OutgoingMsgHandler(topicName, parentApp) {
    override suspend fun handle(payload: Map<String, *>, properties: Map<String, Any>): Map<String, Any?> {
        val rawRunId = properties["run_id"] as String?
        var runUUID: UUID? = <Check rawRunID and parse to UUID>

        val messageId = RevisionService.logNewRevision(DateTime.now(), topicName, payload, runUUID)

        return mapOf(
            "action_flag" to ActionFlags.SEND.name,
            "message_id" to messageId,
            "gatling_run_id" to rawRunId
        )
    }
}

I hope someone here knows what may be causing this issue, because I'm stuck on it.

[qoomon]

I think it is because of the mapping from string to var char, it should rather be mapped to TextColumnType(). May have a look at my implementation https://gist.github.com/qoomon/70bbbedc134fd2a149f1f2450667dc9d

This is the wrong mapping code from your solution

inline fun <reified T> Column<Map<*, *>>.json(jsonKey: JsonKey): Function<T> {
    val columnType = when (T::class) {
        Int::class -> IntegerColumnType()
        String::class -> VarCharColumnType()
        Boolean::class -> BooleanColumnType()
        else -> throw java.lang.RuntimeException("Column type ${T::class} not supported for json field.")
    }

    return json(jsonKey, columnType)
}

[toastyblast]

@qoomon thank you for your swift response! I tried it out just now.

Sadly I'm still getting the exact same error from my previous comment. I thought maybe the inclusion of a DateTime might be causing an issue, so I tried adding that as well:

inline fun <reified T> Column<Map<*, *>>.json(jsonKey: JsonKey): Function<T> {
    val columnType = when (T::class) {
        Boolean::class -> BooleanColumnType()
        Int::class -> IntegerColumnType()
        Float::class -> FloatColumnType()
        Double::class -> DoubleColumnType()
        DateTime::class -> DateColumnType(true)
        String::class -> TextColumnType()
        else -> throw IllegalArgumentException("Type ${T::class} not supported for json fields.")
    }

    return json(jsonKey, columnType)
}

But I had no luck with that either, still getting the same error...

I also tried copying the code from your gist with your implementation you linked, but I can't get it to work due to your override fun setParameter. For me, the super.setParameter expects stmt to be PreparedStatement not PerparedStatementApi. Additionally, ColumnType doesn't have a fun setParameter for me (IColumnType only does, which ColumnType inherits) so the override in your example doesn't work. Even if I change your code to inherit IColumnType instead, the stmt being the wrong type issue still applies.

I'm using, through Gradle, all implementations for the latest versions of the libraries, these being org.postgresql:postgresql:42.2.10, org.jetbrains.exposed:exposed:0.17.7, org.jetbrains.exposed:exposed-core:0.23.1 and org.jetbrains.kotlinx:kotlinx-serialization-runtime:0.20.0

[qoomon]

@toastyblast I just verified that it is compiling. I don't know what's going wrong on you side. However I had another idea what might causing your problem. Do you teach you JSON parse to handle(serialize and deserialize) DateTime? May you can write a tiny test so I can have a look at that reproduce your error?

[toastyblast]

@qoomon I'm currently not doing anything of the sort, as far as I'm aware. I'm using Gson to do the parsing, and I don't know how to make that handle my JodaTime DateTime objects (as far as I'm aware that should just use JodaTime's toString and print out the ISO standard timestamp, but I don't know) - Do you have any idea of this?

Also, I find that odd with the compilation, maybe you have some additional libraries I don't have, or different versions than what I put in my previous comment, I have no clue.

[qoomon]

you could make use of this lib https://github.com/gkopff/gson-jodatime-serialisers or do it on your own like this

GsonBuilder()
    .registerTypeAdapter(LocalDateTime::class.java, object : JsonSerializer<LocalDateTime> {
        override fun serialize(
            src: LocalDateTime,
            typeOfSrc: Type,
            context: JsonSerializationContext
        ): JsonElement = JsonPrimitive(src.format(DateTimeFormatter.ISO_LOCAL_DATE_TIME))
    })
    .registerTypeAdapter(LocalDateTime::class.java, object : JsonDeserializer<LocalDateTime> {
        override fun deserialize(
            json: JsonElement,
            typeOfT: Type,
            context: JsonDeserializationContext
        ): LocalDateTime = LocalDateTime.parse(json.asString, DateTimeFormatter.ISO_LOCAL_DATE_TIME)
    })

however I would suggest to use kotlinx.serialization or moshi as json framework for kotlin

[Rubyj]

@qoomon @toastyblast The discrepancies between your errors looks like, to me, you are both using different versions of kotlin

[toastyblast]

It's nearing midnight for me, so I'll check out the library tomorrow, @qoomon

As for Kotlin, I'm running one of the latest versions, don't know exactly which one but the project started a few months ago, and I got the latest at the time. I'll drop that tomorrow as well, if need be!

I'm currently running Kotlin version 1.3.61, with the target JVM version 1.8.

[toastyblast]

After some more testing I think what is causing the issue is PSQL simply not recognising the incoming string as being JSON/JSONB castable, due to the error clearly stating it's seeing it as a VARCHAR (character variable) and not JSONB. The timestamp issue @qoomon thought up I thought was promising too, but on review I realised my payload map never had DateTimes in them. I still attempted your idea however, of adding that library, but again it had no effect since I never included timestamps anyways.

In the end I've decided to change my table's JSONB field to TEXT and simply store my JSON-valid string. The application that queries this table never actually accesses the JSON anyways, it was more me trying to be more specific. And if the querying application does need the strings as JSON in the future, it can simply parse them itself and process them, it doesn't need them to be stored as JSON for this, since it is never going to query the DB on fields of the payload anyways.

Still, thanks for the help guys, particularly @qoomon and @Rubyj ! The issue does stand, but I hope nobody runs into it in the future. I for one have no clue how to fix or prevent it except for just storing my valid json string in a TEXT field, until Exposed natively supports JSON & JSONB.

[jeffgnpc]

@qoomon Thanks for sharing your code. I have included it my project and I'm able to create simple CRUD operations. I see you have add json and contains methods, but I can't understand how they are suppose to be used. Can you please include examples of how those are suppose to be used? Thanks.