Search code examples
javapostgresqlspring-webfluxjsonbr2dbc

How to extract jsonb from Postgresql to Spring webflux using R2dbc

So I had this idea that's way over my head really since I've only been programming for a short while but I wanted to build a reactive Spring webflux app that exposes json endpoints to a react frontend.

The problem started when I decided to use the jsonb format in Postgres because I thought I might use json all the way from the DB up until the frontend layer.

When I try to SELECT the table with jsonb using the reactive R2dbc drivers I get the following error:

Caused by: java.lang.IllegalArgumentException: 3802 is not a valid object id

I have a table in postgres that looks like this:

Column  |  Type   | Collation | Nullable |           Default
---------+---------+-----------+----------+------------------------------
 id      | integer |           | not null | generated always as identity
 details | jsonb   |           |          |
Indexes:
    "snacks_new_pkey" PRIMARY KEY, btree (id)

So if I extract this as text to Spring webflux it works fine since it's no longer json.

"SELECT id, details->>'name' as NAME, details->>'price' AS PRICE, details->>'quantity' AS QUANTITY FROM snacks_new"

I've seen some examples on how to convert jsonb to an json object using the older blocking drivers but I can't get the same to work with the newer non-blocking ones, I can't access them in any way.

So I have 2 questions really, how can I SELECT a table that contains jsonb using reactive drivers, and am I wasting my time trying to do this, is extracting the json as text and creating a normal POJO from it enough?

Thanks for your time!

Solution

  • Update: Please upgrade to R2DBC Postgres 0.8.0.RC1.

    The driver added recently support for JSON and JSONB types. You can consume JSON either as String, byte[] or io.r2dbc.postgresql.codec.Json type:

    // Read as Json
connection.createStatement("SELECT my_json FROM my_table")
        .execute()
        .flatMap(it -> it.map((row, rowMetadata) -> row.get("my_json", Json.class)))
        .map(Json::asString)

// Read as String
connection.createStatement("SELECT my_json FROM my_table")
        .execute()
        .flatMap(it -> it.map((row, rowMetadata) -> row.get("my_json", String.class)))

// Write JSON
connection.createStatement("INSERT INTO my_table (my_json) VALUES($1)")
        .bind("$1", Json.of("{\"hello\": \"world\"}"))
        .execute()

// Write JSON as String using ::JSON casting
connection.createStatement("INSERT INTO my_table (my_json) VALUES($1::JSON)")
        .bind("$1", "{\"hello\": \"world\"}")
        .execute()

    Please note that when you want to bind JSON values for a SELECT, INSERT or UPDATE, then you must either use the driver Json type or cast the binding value with $1::JSON to JSON.

    You can also provide your own JsonCodec implementation leveraging the driver's CodecRegistrar if you e.g. want to use GSON or Jackson to map serialize/deserialize values on the driver level.

    References: