-
-
Save xtexChooser/5ead6ff7f7b419c57efaa59cf2fef492 to your computer and use it in GitHub Desktop.
/** | |
* 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) |
@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.
@xtexChooser Could you please list all the required dependencies to use this gist?
Hello @Lysoun
- kotlinx.serialization.json
- exposed-core
- H2 database, you can remove code related to this if you do not need H2DB support
Hello @Lysoun
- kotlinx.serialization.json
- exposed-core
- H2 database, you can remove code related to this if you do not need H2DB support
Thank you!
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?
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.
@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)