Wrong value inserted in table with jsonb column

[DavideD]

For PostgreSQL and Vert.x SQL client 4.5.10

Given the following table:

create table BookWithJson (id integer not null, title varchar(255), author jsonb, primary key (id))

The code:

String sql = "insert into BookWithJson (author,price,title,id) values (cast($1 as jsonb),$2,$3,$4)";
Tuple parameters = Tuple.of( "{\"name\":\"Peng\",\"surname\":\"Shepherd\"}", "The Book of M", 3 );
sqlConnection.preparedQuery( sql ).execute( parameters );

will insert the row (using psql):

 id |     title     |                        author                        
----+---------------+-------------------------------------------------------------------------
  5 | The Book of M | "{\"name\":\"Peng\",\"surname\":\"Shepherd\"}"

instead of:

 id |     title     |                        author                        
----+---------------+-------------------------------------------------------------------------
  5 | The Book of M | {"name":"Peng","surname":"Shepherd"}

I'm not even sure how it's possible, because it's inserting a string in a jsonb column. I will try to create a test if necessary.

[tsegismont]

It's not a bug: a plain string is a valid JSON value, as per rfc 7159.

What happens here is that a string value is stored in the jsonb column, regardless of its content.

If you want the content of the string to be converted to a JSON object, you must do it in the Java code with new JsonObject(string) or in the SQL query

[DavideD]

Thanks. I was surprised because it didn't seem to work when I tried, but it was just a typo I made.

[DavideD]

@tsegismont On second thought, are we sure that this is not a bug?

It's true that I'm passing a parameter as String, but the SQL query is calling a function that should convert it to a proper JSON object. How come this is not happening? It seems like the cast( $1 as jsonb) is ignored.

[tsegismont]

cast is not ignored, it correctly converts a string to jsonb. When the query is prepared, postgres doesn't know what kind of data it may expect, so when vert.x sends a string on the wire, it considers it as valid jsonb content.

If you want a conversion of the content of the string, give Pg a hint with cast($1::text as jsonb)