Json addition for Exposed.

jsonb.kt

import com.fasterxml.jackson.databind.ObjectMapper
import org.jetbrains.exposed.sql.*
import org.jetbrains.exposed.sql.Function
import org.jetbrains.exposed.sql.vendors.MariaDBDialect
import org.jetbrains.exposed.sql.vendors.MysqlDialect
import org.jetbrains.exposed.sql.vendors.PostgreSQLDialect
import org.postgresql.util.PGobject
import java.sql.PreparedStatement
import java.sql.Types

fun <T : Any> Table.jsonb(name: String, klass: Class<T>, jsonMapper: ObjectMapper): Column<T> {
    return if (databaseDialect is PostgreSQLDialect) {
        registerColumn(name, PostgreSQLJson(klass, jsonMapper))
    } else {
        registerColumn(name, MariaDBStringJson(klass, jsonMapper))
    }
}

private class PostgreSQLJson<out T : Any>(private val klass: Class<T>, private val jsonMapper: ObjectMapper) : ColumnType() {
    override fun sqlType() = "jsonb"

    override fun setParameter(stmt: PreparedStatement, index: Int, value: Any?) {
        val obj = PGobject()
        obj.type = "jsonb"
        obj.value = value as String
        stmt.setObject(index, obj)
    }

    override fun valueFromDB(value: Any): Any {
        value as PGobject
        return try {
            jsonMapper.readValue(value.value, klass)
        } catch (e: Exception) {
            e.printStackTrace()
            throw RuntimeException("Can't parse JSON: $value")
        }
    }

    override fun notNullValueToDB(value: Any): Any = jsonMapper.writeValueAsString(value)
    override fun nonNullValueToString(value: Any): String = "'${jsonMapper.writeValueAsString(value)}'"
}

private class MariaDBStringJson<out T : Any>(private val klass: Class<T>, private val jsonMapper: ObjectMapper) :
    ColumnType() {
    override fun sqlType() = "TEXT"

    override fun setParameter(stmt: PreparedStatement, index: Int, value: Any?) {
        if (value == null) stmt.setNull(index, Types.VARCHAR)
        if (value !is String) throw java.lang.RuntimeException("Should be string.")
        stmt.setString(index, value)
    }

    override fun valueFromDB(value: Any): Any {
        value as String
        return try {
            jsonMapper.readValue(value, klass)
        } catch (e: Exception) {
            e.printStackTrace()
            throw RuntimeException("Can't parse JSON: $value")
        }
    }

    override fun notNullValueToDB(value: Any): Any = jsonMapper.writeValueAsString(value)
    override fun nonNullValueToString(value: Any): String = "'${jsonMapper.writeValueAsString(value)}'"
}

class JsonKey(val key: String) : Expression<String>() {
    init {
        if (!key.matches("[a-zA-Z]+".toRegex())) throw IllegalArgumentException("Only simple json key allowed.")
    }

    override fun toSQL(queryBuilder: QueryBuilder) = key
}

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

fun <T> Column<Map<*, *>>.json(jsonKey: JsonKey, columnType: IColumnType): Function<T> {
    return when (databaseDialect) {
        is PostgreSQLDialect -> PostgreSQLJsonVal(this, jsonKey, columnType)
        is MysqlDialect, is MariaDBDialect -> MariaDBTextJsonVal(this, jsonKey, columnType)
        else -> throw RuntimeException("Database $databaseDialect not supported for json yet.")
    }
}

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

private class MariaDBTextJsonVal<T>(val expr: Expression<*>, val jsonKey: JsonKey, override val columnType: IColumnType) : Function<T>(columnType) {
    override fun toSQL(queryBuilder: QueryBuilder) = "JSON_VALUE(${expr.toSQL(queryBuilder)}, '\$.${jsonKey.key}')"
}