Reactive pg client with mutiny can not insert into a jsonb column

[vankofalcon]

Quarkus version: 1.7.0

I have a simple table in Postgresql 11 with a jsonb column. When I try to insert from my java code the inserted value is treated as an escaped string in postgresql and my json queries don't work. It seems that the inserted data is escaped.

String data = jsonb.toJson(myObj);
dbClient.preparedQuery("INSERT INTO object_data (data) " +
                "VALUES ($1::JSON) RETURNING (id)")
                .execute(Tuple.of(data))
                .map(rs -> rs.iterator().next().getLong("id"));

When I try to insert the same json string form the command line I get the expected behavior and I can query postgresql using the standard json syntax.

So, is that a BUG in the quarkus pg reactive client or I'm doing something wrong?

[quarkusbot]

/cc @cescoffier

[cescoffier]

@tsegismont any idea?

[tsegismont]

@vankofalcon can you try:

String data = jsonb.toJson(myObj);
dbClient.preparedQuery("INSERT INTO object_data (data) " +
                "VALUES (to_json($1)) RETURNING (id)")
                .execute(Tuple.of(data))
                .map(rs -> rs.iterator().next().getLong("id"));

or with Vert.x JsonObject type:

JsonObject data = ...;
dbClient.preparedQuery("INSERT INTO object_data (data) " +
                "VALUES ($1) RETURNING (id)")
                .execute(Tuple.of(data))
                .map(rs -> rs.iterator().next().getLong("id"));

[vankofalcon]

String data & to_json($1:json) -> not working javax.json.JsonObject -> Exception: can not be coerced to the expected class = [java.lang.Object] for encoding io.vertx.core.json.JsonObject -> works like a charm :+1:

thanks for the hint @tsegismont I still think that's a bug because inserting String with $1::JSON should definitely work. Wdyt?

[tsegismont]

String data & to_json($1:json) -> not working javax.json.JsonObject -> Exception: can not be coerced to the expected class = [java.lang.Object] for encoding

Have you tried to_json($1) with string ?

io.vertx.core.json.JsonObject -> works like a charm +1

Great

still think that's a bug because inserting String with $1::JSON should definitely work. Wdyt?

Can you try the same query with JDBC datasource?

[vankofalcon]

Have you tried to_json($1) with string ? not working Can you try the same query with JDBC datasource? works

[tsegismont]

Thanks for the details, I'll come back to you as soon as I can

[cescoffier]

@tsegismont Did you look into this one?

[tsegismont]

No. It is possible to insert when using a Vert.x JsonObject instead of String. I'm busy with Vert.x 4 release for now, but I can come back to this later this week.

[tsegismont]

@vankofalcon @cescoffier I did some experiments and here are my findings.

If you run this with the Postgres JDBC client:

try (PreparedStatement ps = conn.prepareStatement("INSERT INTO object_data (data)  VALUES (to_json(?)) RETURNING (data)")) {
RETURNING (data)")) {
  ps.setString(1, "{\"json\": \"string\"}");
  ps.execute();
  try (ResultSet rs = ps.getResultSet()) {
    while (rs.next()) {
      PGobject object = (PGobject) rs.getObject(1);
      System.out.println("object = " + object.getValue());
    }
  }
}

You will see:

object = "{\"json\": \"string\"}"

If you want to make Postgres convert the JSON encoded string to an actual JSON object, you need to do:

try (PreparedStatement ps = conn.prepareStatement("INSERT INTO object_data (data)  VALUES (CAST(?::text AS JSONB)) RETURNING (data)")) {
  ps.setString(1, "{\"json\": \"string\"}");
  ps.execute();
  try (ResultSet rs = ps.getResultSet()) {
    while (rs.next()) {
      PGobject object = (PGobject) rs.getObject(1);
      System.out.println("object = " + object.getValue());
    }
  }
}

And then you will see:

object = {"json": "string"}

Notice the double quotes around the value are no longer present.

Now back to the Reactive Pg Client, if you run this:

PreparedQuery<RowSet<Row>> insertQuery = pgConnection.preparedQuery("INSERT INTO object_data (data)  VALUES (to_json($1)) RETURNING (data)");
insertQuery.execute(Tuple.of("{\"json\": \"string\"}"), ctx.asyncAssertSuccess(rs -> {
  Row row = rs.iterator().next();
  Object value = row.getJsonObject(0);
  System.out.println("value.getClass() = " + value.getClass());
  System.out.println("value = " + value);
}));

You will get:

io.vertx.pgclient.PgException: { "message": "could not determine polymorphic type because input has type unknown", "severity": "ERROR", "code": "42804", "file": "parse_coerce.c", "line": "1866", "routine": "enforce_generic_type_consistency" }

The problem is that the Reactive Pg Client prepares the query without sending the parameter types. I'll check with the team why it doesn't.

However if you execute this:

PreparedQuery<RowSet<Row>> insertQuery = pgConnection.preparedQuery("INSERT INTO object_data (data)  VALUES (CAST($1::text AS JSONB)) RETURNING (data)");
insertQuery.execute(Tuple.of("{\"json\": \"string\"}"), ctx.asyncAssertSuccess(rs -> {
  Row row = rs.iterator().next();
  JsonObject value = row.getJsonObject(0);
  System.out.println("value = " + value);
}));

You will get as expected:

value = {"json":"string"}

So we do have an issue with prepared queries in some cases, but it's not related to JSONB type or the inability to insert into a jsonb column.

For this reason @cescoffier , I believe we can close this issue. I'll create another issue on the Reactive Pg Client repo to track the problem of preparing statements without sending the param types.

[tsegismont]

@cescoffier @vankofalcon filed https://github.com/eclipse-vertx/vertx-sql-client/issues/851

[cescoffier]

Thanks @tsegismont

[tsegismont]

@cescoffier @vankofalcon this fix has been pushed upstream https://github.com/eclipse-vertx/vertx-sql-client/pull/855 It will be present in Vert.x 3.9.6.

[cescoffier]

Closing.... fixed in december.