It is possible to use JSON or JSONB postgresql datatypes with spring r2dbc manually?

Question

Im building a new api with spring boot, webflux and r2dbc (postgresql). One of my tables will have a json or jsonb field, to store a dynamic json document.

As for now, r2dbc does not support json datatypes for postgresql database. Im wondering if its possible to use it anyway, writing some code for the repository.

I have created a simple test project, to try this out, but with no luck yet. I was able to save the JSON field by using the org.springframework.data.r2dbc.core.DatabaseClient.execute() and wrapping the json field with to_json() in the query, but i couldnt read it back later on.

The test entity:

@Data
@NoArgsConstructor
@AllArgsConstructor
@Table("Test")
public class Test {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id", columnDefinition = "serial")
    private Long id;
    @Column(name = "data", columnDefinition = "json")
    private String data;

}

Save method working:

public Mono<Integer> save(Test test) {
        return databaseClient.execute("INSERT INTO test (data) VALUES(to_json($1))")
                .bind("$1", test.getData())
                .fetch()
                .rowsUpdated();
    }

Exception when reading it back:

java.lang.IllegalArgumentException: Cannot decode value of type java.lang.Object
    at io.r2dbc.postgresql.codec.DefaultCodecs.decode(DefaultCodecs.java:97)
    at io.r2dbc.postgresql.PostgresqlRow.get(PostgresqlRow.java:88)
    at io.r2dbc.spi.Row.get(Row.java:63)
    at org.springframework.data.r2dbc.convert.ColumnMapRowMapper.getColumnValue(ColumnMapRowMapper.java:100)
    at org.springframework.data.r2dbc.convert.ColumnMapRowMapper.apply(ColumnMapRowMapper.java:59)
    at org.springframework.data.r2dbc.convert.ColumnMapRowMapper.apply(ColumnMapRowMapper.java:44)
    at io.r2dbc.postgresql.PostgresqlResult.lambda$map$0(PostgresqlResult.java:71)

Answer 1

Use io.r2dbc.postgresql.codec.Json as your entity property type, check my post about this.