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)

As per [this](https://r2dbc.io/2019/10/26/r2dbc-0-8-rc1-1-released) blog, new Release candidate supports it. — Avinash Anand, Oct 06 '19 at 04:21
Sorry, i think they removed the release notes. but now on the [README](https://github.com/r2dbc/r2dbc-postgresql), it says that it supports `JSON` and `JSONB`. New Codec has been added - `io.r2dbc.postgresql.codec.Json`. New [link](https://r2dbc.io/2019/09/26/r2dbc-0-8-rc1-1-released) — Avinash Anand, Oct 08 '19 at 03:46
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, Oct 08 '19 at 18:58

score 4 · Answer 1 · answered Oct 08 '20 at 03:48

4

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

answered Oct 08 '20 at 03:48

Hantsy

8,006
7
64
109

score 1 · Answer 2 · answered Aug 01 '19 at 17:51

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();
}

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

2 Answers2