Our brief pass at implementing JSONB column support in Exposed.

JsonbColumnType.kt

import com.fasterxml.jackson.core.type.TypeReference
import com.fasterxml.jackson.databind.ObjectMapper
import org.jetbrains.exposed.sql.*
import org.jetbrains.exposed.sql.Function
import org.jetbrains.exposed.sql.statements.api.PreparedStatementApi
import org.postgresql.util.PGobject
import org.koin.java.KoinJavaComponent.inject

fun Table.customProperties(name: String, objectMapper: ObjectMapper): Column<Map<String, Any>> =
    registerColumn(name, PgJsonColumnType(objectMapper))

fun Column<Map<String, Any>>.property(fieldName: String): JsonValue {
    val objectMapper: ObjectMapper by inject(ObjectMapper::class.java)
    return JsonValue(this, PgJsonColumnType(objectMapper), fieldName)
}

class JsonValue(
    private val expr: Expression<Map<String, Any>>,
    override val columnType: PgJsonColumnType,
    private val fieldName: String
) :
    Function<Any>(columnType) {
    override fun toQueryBuilder(queryBuilder: QueryBuilder) = queryBuilder {
        append(expr)
        append(" #> '{$fieldName}'")
    }
}

class JsonContainsOp(expr1: Expression<*>, expr2: Expression<*>) : ComparisonOp(expr1, expr2, "??")

infix fun <T> JsonValue.contains(t: T): JsonContainsOp =
    JsonContainsOp(this, wrapParam(t))

infix fun <T> JsonValue.jsonEq(t: T): Op<Boolean> =
    compare(t) { lhs: Expression<*>, rhs: Expression<*> -> EqOp(lhs, rhs) }

infix fun JsonValue.jsonGreater(t: Number): Op<Boolean> =
    compare(t) { lhs: Expression<*>, rhs: Expression<*> -> GreaterOp(lhs, rhs) }

@OptIn(ExperimentalUnsignedTypes::class)
private fun <T> wrapParam(value: T): Expression<*> = when (value) {
    is Boolean -> booleanParam(value)
    is Byte -> byteParam(value)
    is UByte -> ubyteParam(value)
    is Short -> shortParam(value)
    is UShort -> ushortParam(value)
    is Int -> intParam(value)
    is UInt -> uintParam(value)
    is Long -> longParam(value)
    is ULong -> ulongParam(value)
    is Float -> floatParam(value)
    is Double -> doubleParam(value)
    else -> QueryParameter(value, TextColumnType())
}

private fun <T> JsonValue.compare(rhs: T, op: (lhs: Expression<*>, rhs: Expression<*>) -> ComparisonOp): Op<Boolean> {
    return when (rhs) {
        is Boolean -> op(this.castTo<Boolean>(BooleanColumnType()), wrapParam(rhs))
        is Byte -> op(this.castTo<Byte>(ByteColumnType()), wrapParam(rhs))
        is Int -> op(this.castTo<Int>(IntegerColumnType()), wrapParam(rhs))
        is Long -> op(this.castTo<Long>(LongColumnType()), wrapParam(rhs))
        else -> throw Exception("Unsupported comparison type: $rhs")
    }
}

class PgJsonColumnType(private val objectMapper: ObjectMapper) : ColumnType() {
    override fun sqlType(): String = "jsonb"

    override var nullable = false

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

    override fun valueFromDB(value: Any): Any = when (value) {
        is HashMap<*, *> -> value
        is Map<*, *> -> value
        is PGobject -> {
            val json = value.value
            objectMapper.readValue(json, object : TypeReference<Map<String, Any>>() {})
        }
        else -> throw Exception("Object passed to valueFromDB for PgJsonColumnType was not of an expected type")
    }

    override fun notNullValueToDB(value: Any): Any =
        objectMapper.writeValueAsString(value)

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