Skip to content

Instantly share code, notes, and snippets.

@xtexChooser
Last active May 5, 2023 16:37
Show Gist options
  • Save xtexChooser/5ead6ff7f7b419c57efaa59cf2fef492 to your computer and use it in GitHub Desktop.
Save xtexChooser/5ead6ff7f7b419c57efaa59cf2fef492 to your computer and use it in GitHub Desktop.
JSON type for JetBrains/Exposed
/**
* JSON and JSONB support for github.com/JetBrains/Exposed.
*
* Tested with
* - github.com/pgjdbc/pgjdbc 42.2.x
* - github.com/mysql/mysql-connector-j
* - github.com/h2database/h2database
*
* Based on gist.github.com/qoomon/70bbbedc134fd2a149f1f2450667dc9d
* Thanks for everyone in github.com/JetBrains/Exposed#127
* Released on https://gist.github.com/xtexChooser/5ead6ff7f7b419c57efaa59cf2fef492
*/
import kotlinx.serialization.InternalSerializationApi
import kotlinx.serialization.KSerializer
import kotlinx.serialization.json.Json
import kotlinx.serialization.serializer
import org.jetbrains.exposed.sql.*
import org.jetbrains.exposed.sql.Function
import org.jetbrains.exposed.sql.vendors.currentDialect
import org.h2.value.ValueJson as H2ValueJson
class JsonColumnType<T : Any>(val json: Json, val serializer: KSerializer<T>, val type: Type = Type.JSONB) :
StringColumnType() {
override fun sqlType(): String = when (type) {
Type.JSON -> "JSON"
Type.JSONB -> "JSONB"
Type.TEXT -> currentDialect.dataTypeProvider.textType()
}
override fun valueFromDB(value: Any) = when (val v = super.valueFromDB(value)) {
is String -> json.decodeFromString(serializer, v)
is PGobject -> json.decodeFromString(serializer, v.value!!)
is com.mysql.cj.xdevapi.JsonValue -> json.decodeFromString(serializer, v.toFormattedString())
is ValueJson -> json.decodeFromString(serializer, v.string)
else -> v
}
override fun notNullValueToDB(value: Any): Any = when (currentDialect) {
is PostgreSQLDialect ->
PGobject().apply {
type = sqlType().lowercase()
setValue(nonNullValueToString(value))
}
is MysqlDialect -> JsonParser.parseDoc(nonNullValueToString(value))
is H2Dialect -> ValueJson.get(nonNullValueToString(value))
else -> error("Unsupported dialect: $currentDialect")
}
@Suppress("UNCHECKED_CAST")
override fun nonNullValueToString(value: Any) = json.encodeToString(serializer, value as T)
override fun valueToString(value: Any?): String = when (value) {
is Iterable<*> -> nonNullValueToString(value)
else -> super.valueToString(value)
}
enum class Type {
JSON, JSONB, TEXT
}
}
inline fun <reified T : Any> Table.json(name: String, json: Json): Column<T> =
@OptIn(InternalSerializationApi::class)
json(name, T::class.serializer(), json)
fun <T : Any> Table.json(name: String, serializer: KSerializer<T>, json: Json) =
registerColumn<T>(name, JsonColumnType(json, serializer))
class JsonValue<T>(
val expr: Expression<*>,
override val columnType: ColumnType,
val jsonPath: List<String>
) : Function<T>(columnType) {
override fun toQueryBuilder(queryBuilder: QueryBuilder) = queryBuilder {
val json = (columnType is JsonColumnType<*>) && (columnType.type != JsonColumnType.Type.TEXT)
if (json) append("(")
append(expr)
append(" #>")
if (json) append(">")
append(" '{${jsonPath.joinToString { escapeFieldName(it) }}}'")
if (json) append(")::${columnType.sqlType()}")
}
private fun escapeFieldName(value: String) = value
.map {
when (it) {
'\"' -> "\\\""
'\r' -> "\\r"
'\n' -> "\\n"
else -> it
}
}.joinToString("").let { "\"$it\"" }
}
inline fun <reified T : Any> Column<*>.json(vararg jsonPath: String): JsonValue<T> {
val columnType = when (T::class) {
Boolean::class -> BooleanColumnType()
Byte::class -> ByteColumnType()
Short::class -> ShortColumnType()
Int::class -> IntegerColumnType()
Long::class -> LongColumnType()
Float::class -> FloatColumnType()
Double::class -> DoubleColumnType()
String::class -> TextColumnType()
else -> @OptIn(InternalSerializationApi::class)
JsonColumnType(Json.Default, T::class.serializer())
}
return JsonValue(this, columnType, jsonPath.toList())
}
class JsonContainsOp(expr1: Expression<*>, expr2: Expression<*>) : ComparisonOp(expr1, expr2, "??")
infix fun <T> JsonValue<Any>.contains(t: T): JsonContainsOp =
JsonContainsOp(this, SqlExpressionBuilder.run { wrap(t) })
infix fun <T> JsonValue<Any>.contains(other: Expression<T>): JsonContainsOp =
JsonContainsOp(this, other)
@uvinw
Copy link

uvinw commented Aug 18, 2022

@xtexChooser would you care to add a usage sample? I'd prefer converting between jsonb and String (even though Kotlinx.serialization is in the project)

@xtexChooser
Copy link
Author

@xtexChooser would you care to add a usage sample? I'd prefer converting between jsonb and String (even though Kotlinx.serialization is in the project)

Hello, the usage is easy, just call Table#json, see here for more.

To convert jsonb to string, just modify the nonNullValueToString and valueFromDB to remove the serializer calls, such as this, an example is in above file.

@Lysoun
Copy link

Lysoun commented Sep 14, 2022

@xtexChooser Could you please list all the required dependencies to use this gist?

@xtexChooser
Copy link
Author

Hello @Lysoun

  1. kotlinx.serialization.json
  2. exposed-core
  3. H2 database, you can remove code related to this if you do not need H2DB support

@Lysoun
Copy link

Lysoun commented Sep 16, 2022

Hello @Lysoun

  1. kotlinx.serialization.json
  2. exposed-core
  3. H2 database, you can remove code related to this if you do not need H2DB support

Thank you!

@afgarcia86
Copy link

I really want to use this but I appear to be missing something critical about the setup. @xtexChooser would you be open into explaining the usage / requirements in more detail?

@xtexChooser
Copy link
Author

Just like other impls in that issue in Exposed,

val col = json("column", Json)

References under org.h2 could be removed if H2 database is not needed.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment