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

2
votes

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)
2
javapostgresqljava-11r2dbcspring-data-r2dbc
As per this blog, new Release candidate supports it. – Avinash Anand
Above link is dead... – Cesar Angiolucci
Sorry, i think they removed the release notes. but now on the README, it says that it supports JSON and JSONB. New Codec has been added - io.r2dbc.postgresql.codec.Json. New link – Avinash Anand
I just tried this, but I wasn't able to use this approach with spring-data-r2dbc just yet. The r2dbc support is there, but not yet in the spring-data-r2dbc abstraction. – Cesar Angiolucci

2 Answers

2
votes

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

1
votes

I was able to do this by casting the json/jsonb to TEXT via query. Im not sure if this is the right choice, but it works.

public Mono<Test> findById(Long id) {
    return databaseClient.execute("SELECT id, data::TEXT FROM test WHERE id = $1")
            .bind("$1", id)
            .map(row -> new Test(
                          row.get("id", Long.class),
                          row.get("data", String.class)
                        )
            )
            .one();
}