How to map custom object to jsonb column with R2DBC driver?

[byungcho]

Hi team,
I'm trying to use r2dbc entity and repository with a table which has 'jsonb' type column.
But, I have failed in converting custom data object to json data and successfully storing it to jsonb column so far. I'm suspicious if postgresql driver supports object mapping to jsonb column correctly.
Can you please help me with this?

The table looks like below.

CREATE TABLE jsonb_example (
    id integer NOT NULL,
    name character varying(10) NOT NULL,
    reported_date date NOT NULL,
    measure jsonb
);

I defined entity model in Kotlin like this. And I hope the measure: Measure? field is converted to json string and stored in the table correctly.

@Table("jsonb_example")
data class JsonbExample (
    @Id
    @Column("id")
    val id: Long? = null,

    val name: String,
    val reportedDate: LocalDate,
    var measure: Measure? = null,
): Serializable { }

With a repository definition.

@Repository
interface JsonbExampleRepository: CoroutineCrudRepository<JsonbExample, Long> {
}

The class 'Measure' contains some data.

data class Measure(
    @JsonProperty("Code")
    val code: String,
    @JsonProperty("Name")
    val name: String,
    @JsonProperty("Abbreviation")
    val abbreviation: String,
) { }

I hope the Measure object is converted to json string and stored to table correctly.
So I defined the 'Converter's like this. (Measure <-> Json)

@WritingConverter
class MeasureEncoder: Converter<Measure, Json?> {
    private val objectMapper: ObjectMapper = ObjectMapper()

    override fun convert(source: Measure): Json? {
        return runCatching {
            Json.of(objectMapper.writeValueAsString(source))
        }.getOrNull()
    }
}

@ReadingConverter
class MeasureDecoder: Converter<Json, Measure> {
    private val objectMapper: ObjectMapper = ObjectMapper()
    override fun convert(source: Json): Measure {
        return runCatching {
            objectMapper.readValue<Measure>(source.asString())
        }.getOrElse { Measure.create() }
    }
}

And, registered the converters in R2dbcConfiguration.

@Configuration
@EnableR2dbcRepositories
class R2dbcConfiguration {
    companion object {
        const val DRIVER_NAME = "postgresql"
    }

    @Bean
    fun r2dbcMappingContext(
        namingStrategy: ObjectProvider<NamingStrategy>,
        r2dbcCustomConversions: R2dbcCustomConversions
    ): R2dbcMappingContext? {
        val relationalMappingContext = R2dbcMappingContext(
            namingStrategy.getIfAvailable { NamingStrategy.INSTANCE })
        relationalMappingContext.setSimpleTypeHolder(r2dbcCustomConversions.simpleTypeHolder)
        return relationalMappingContext
    }

    @Bean
    fun r2dbcCustomConversions(): R2dbcCustomConversions {
        val converters = ArrayList<Any>().apply {
            addAll(PostgresDialect.INSTANCE.converters)
            addAll(R2dbcCustomConversions.STORE_CONVERTERS)
            add(MeasureEncoder())
            add(MeasureDecoder())
        }
        return R2dbcCustomConversions(R2dbcCustomConversions.STORE_CONVERSIONS, converters)
    }

    @Bean
    fun r2dbcConverter(mappingContext: R2dbcMappingContext?, r2dbcCustomConversions: R2dbcCustomConversions?) =
        MappingR2dbcConverter(mappingContext!!, r2dbcCustomConversions!!)
}

With above codes, when I try to save JsonbExample object using the repository,
the insert query is executed successfully but the 'measure' field is always null.

With some debugging, I figured out that the converters works fine which means Measure object is correctly converted to Json object, but at the very final stage of building insert query, the value of measure field becomes 'null' because 'Json' type is not registered in r2dbc SimpleTypeHolder and r2dbc QueryMapper doesn't have any idea of how to proceed with Json type so it makes the value 'null'.

I also tried with the Converter from Measure to String type (not Json), but at this time, the query execution throws exeption like this.

executeMany; bad SQL grammar [INSERT INTO JSONB_EXAMPLE (name, reported_date, measure) VALUES ($1, $2, $3)]; nested exception is io.r2dbc.postgresql.ExceptionFactory$PostgresqlBadGrammarException: [42804] column "measure" is of type jsonb but expression is of type character varying
org.springframework.r2dbc.BadSqlGrammarException: executeMany; bad SQL grammar [INSERT INTO JSONB_EXAMPLE (name, reported_date, measure) VALUES ($1, $2, $3)]; nested exception is io.r2dbc.postgresql.ExceptionFactory$PostgresqlBadGrammarException: [42804] column "measure" is of type jsonb but expression is of type character varying
	at app//org.springframework.r2dbc.connection.ConnectionFactoryUtils.convertR2dbcException(ConnectionFactoryUtils.java:235)
	Suppressed: The stacktrace has been enhanced by Reactor, refer to additional information below: 
Assembly trace from producer [reactor.core.publisher.FluxOnErrorResume] :
	reactor.core.publisher.Flux.onErrorMap
	org.springframework.r2dbc.core.DefaultDatabaseClient.inConnectionMany(DefaultDatabaseClient.java:146)
Error has been observed at the following site(s):
	*__Flux.onErrorMap ⇢ at org.springframework.r2dbc.core.DefaultDatabaseClient.inConnectionMany(DefaultDatabaseClient.java:146)
	|_                 ⇢ at org.springframework.r2dbc.core.DefaultFetchSpec.all(DefaultFetchSpec.java:87)
	|_       Flux.last ⇢ at org.springframework.data.r2dbc.core.R2dbcEntityTemplate.doInsert(R2dbcEntityTemplate.java:649)
	|_    Mono.flatMap ⇢ at org.springframework.data.r2dbc.core.R2dbcEntityTemplate.doInsert(R2dbcEntityTemplate.java:649)
	|_                 ⇢ at org.springframework.data.r2dbc.core.R2dbcEntityTemplate.lambda$null$6(R2dbcEntityTemplate.java:591)
	*_____Mono.flatMap ⇢ at org.springframework.data.r2dbc.core.R2dbcEntityTemplate.lambda$doInsert$7(R2dbcEntityTemplate.java:591)
	*_____Mono.flatMap ⇢ at org.springframework.data.r2dbc.core.R2dbcEntityTemplate.doInsert(R2dbcEntityTemplate.java:582)
	|_                 ⇢ at org.springframework.data.r2dbc.core.R2dbcEntityTemplate.insert(R2dbcEntityTemplate.java:575)
...

How can I convert the custom data class object to jsonb column and vice versa?
Can you please teach me what I'm doing wrong?

[byungcho]

I solved the problem by adding PostgresDialect.INSTANCE.simpleTypeHolder to R2dbcCustomConversions constructor.

@Configuration
@EnableR2dbcRepositories
class R2dbcConfiguration {
    companion object {
        const val DRIVER_NAME = "postgresql"
    }

    @Bean
    fun r2dbcMappingContext(
        namingStrategy: ObjectProvider<NamingStrategy>,
        r2dbcCustomConversions: R2dbcCustomConversions
    ): R2dbcMappingContext? {
        val relationalMappingContext = R2dbcMappingContext(
            namingStrategy.getIfAvailable { NamingStrategy.INSTANCE })
        relationalMappingContext.setSimpleTypeHolder(r2dbcCustomConversions.simpleTypeHolder)
        return relationalMappingContext
    }

    @Bean
    fun r2dbcCustomConversions(): R2dbcCustomConversions {
        val converters = ArrayList<Any>().apply {
            addAll(PostgresDialect.INSTANCE.converters)
            addAll(R2dbcCustomConversions.STORE_CONVERTERS)
            add(JsonToMapReadConverter())
            add(MapToJsonWriteConverter())
            add(MeasureEncoder())
            add(MeasureDecoder())
        }
        return R2dbcCustomConversions(
            StoreConversions.of(
                PostgresDialect.INSTANCE.simpleTypeHolder,    // <- this contains Json class object as simple type
                R2dbcCustomConversions.STORE_CONVERTERS),
            converters)
    }

    @Bean
    fun r2dbcConverter(mappingContext: R2dbcMappingContext?, r2dbcCustomConversions: R2dbcCustomConversions?) =
        MappingR2dbcConverter(mappingContext!!, r2dbcCustomConversions!!)