71

I’m struggling to insert a JSON object into my postgres v9.4 DB. I have defined the column called "evtjson" as type json (not jsonb).

I am trying to use a prepared statement in Java (jdk1.8) to insert a Json object (built using JEE javax.json libraries) into the column, but I keep running into SQLException errors.

I create the JSON object using:

JsonObject mbrLogRec = Json.createObjectBuilder().build();
…
mbrLogRec = Json.createObjectBuilder()
                .add("New MbrID", newId)
                .build();

Then I pass this object as a parameter to another method to write it to the DB using a prepared statement. (along with several other fields) As:

pStmt.setObject(11, dtlRec);

Using this method, I receive the following error:

org.postgresql.util.PSQLException: No hstore extension installed. at org.postgresql.jdbc.PgPreparedStatement.setMap(PgPreparedStatement.java:553) at org.postgresql.jdbc.PgPreparedStatement.setObject(PgPreparedStatement.java:1036)

I have also tried:

pStmt.setString(11, dtlRec.toString());
pStmt.setObject(11, dtlRec.toString());

Which produce a different error:

Event JSON: {"New MbrID":29}

SQLException: ERROR: column "evtjson" is of type json but expression is of type character varying

Hint: You will need to rewrite or cast the expression.

But, at least this tells me that the DB is recognizing the column as type JSON. I did try installing the hstore extension, but it then told me that it was not an hstore object.

OracleDocs shows a number of various methods to set the parameter value in the preparedStatement, but I'd rather not try them all if someone knows the answer. (http://docs.oracle.com/javase/8/docs/api/java/sql/PreparedStatement.html) These also reference an additional parameter, SQLType, but I can't find any reference to these.

Should I try setAsciiStream? CharacterStream? CLOB?

Alexander Farber
  • 21,519
  • 75
  • 241
  • 416
MaybeWeAreAllRobots
  • 1,105
  • 2
  • 9
  • 12

8 Answers8

91

This behaviour is quite annoying since JSON strings are accepted without problems when used as literal strings in SQL commands.

There is a already an issue for this in the postgres driver Github repository (even if the problem seems the be the serverside processing).

Besides using a cast (see answer of @a_horse_with_no_name) in the sql string, the issue author offers two additional solutions:

  1. Use a parameter stringtype=unspecified in the JDBC connection URL/options.

This tells PostgreSQL that all text or varchar parameters are actually of unknown type, letting it infer their types more freely.

  1. Wrap the parameter in a org.postgresql.util.PGobject:

 PGobject jsonObject = new PGobject();
 jsonObject.setType("json");
 jsonObject.setValue(yourJsonString);
 pstmt.setObject(11, jsonObject);
wero
  • 32,544
  • 3
  • 59
  • 84
  • 4
    Thanks! I think option 2 is more what I was looking for. If I understand the code correctly, this is more in line with using JSON in the way that postgres intends. However, isn't this including another conversion step from a javax.json object to a string to wrap it in a PG JSON object? (option 2, step 3) It seems like using JSON objects in general is a waste and I'm better off sticking with string manipulation for now. Would I be correct in assuming that option 1 would have broader performance impacts because then the JDBC driver would have to do a lot of inferring? – MaybeWeAreAllRobots Mar 08 '16 at 02:51
  • 1
    @MaybeWeAreAllRobots regarding performance: you should try, it might not be noticeable. – wero Mar 08 '16 at 07:14
  • 2
    @wero, you are not wero, you are hero! – Andremoniy Jan 31 '17 at 12:55
  • 1
    pStmt.setObject(11, dtlRec.toString(), java.sql.Types.OTHER); Also works (as mentioned in the link you provided). – Oren Jun 13 '20 at 23:58
48

You can do it like this and you just need the json string:

Change the query to:

String query = "INSERT INTO table (json_field) VALUES (to_json(?::json))"

And set the parameter as a String.

pStmt.setString(1, json);
Tiago
  • 718
  • 10
  • 16
  • 1
    Thanks! Using `pStmt.setString(1, json);` caused an error in eclipse, but changing it to `pStmt.setString(1, json.toString());` worked! Some follow-ups: What is the difference between using: `to_json(?::json)` and `cast(? as json)` ? Also, is the net result that I'm stuffing text into a JSON object, then converting it back to text for the JDBC driver, then having the DB reconvert it back to JSON, so that the DB can store it as text?? Am I understanding this correctly? And, if so, shouldn't I just keep it all as a string field and text column? – MaybeWeAreAllRobots Mar 08 '16 at 02:45
  • 1
    to_json(?::json) converts a String to valid json, check the docs [here](http://www.postgresql.org/docs/9.5/static/functions-json.html): cast(? as json) seems to just cast the parameter as JSON, docs [here](http://www.postgresql.org/docs/9.3/static/sql-createcast.html). The advantages is that you can [query](http://stackoverflow.com/questions/10560394/how-do-i-query-using-fields-inside-the-new-postgresql-json-datatype) the JSON field directly which you couldn't in a String field, also it enforces a structure, makes sure it's a valid JSON and I believe you can enforce certain fields. – Tiago Mar 08 '16 at 09:50
  • Also PGObject aproach also works from the other response I can't comment on yet, but if you are using something like wildfly (what I use) You might get into trouble: I made this question myself a few months back [here](http://stackoverflow.com/questions/33441201/inserting-postgres-json-column-using-wildfly) in case you are interested. – Tiago Mar 08 '16 at 10:13
  • 1
    Awesome answer! Works very well when dealing with 3rd party access to the SQL DB. Ran into this issue while working with Mirth Connect. – Michael Hobbs Jun 29 '17 at 17:46
  • 5
    why do you need the `to_json()` part?! `VALUES (?::json)` works just fine – pedram bashiri Apr 11 '18 at 19:41
  • How to implement same thing using JPA? – Dhwanil Patel Mar 24 '21 at 13:19
24

You have two options:

  1. Use statement.setString(jsonStr) and then handle the conversion in the sql statement:

    PreparedStatement statement = con.prepareStatement(
      "insert into table (jsonColumn) values (?::json)");
    statement.setString(1, jsonStr);
    
  2. Another option is to use PGobject to create a custom value wrapper.

    PGobject jsonObject = new PGobject();
    PreparedStatement statement = con.prepareStatement(
      "insert into table (jsonColumn) values (?)");
    jsonObject.setType("json");
    jsonObject.setValue(jsonStr);
    statement.setObject(1, jsonObject);
    

I personally prefer the latter as the query is cleaner

Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
pedram bashiri
  • 1,286
  • 15
  • 21
  • Good point but option #1 is also quite clean, plus: it does not introduce compile time dependency on postgres – user3186301 Jun 18 '22 at 22:10
  • I initially used #2, but then when I wanted to write integration tests in Spring using a H2 database, PgObject does not play nice with H2. So switched to option #1. Upon switching and executing the integration test, h2 does not like the syntax `?::json` (it instead needs the format `? FORMAT JSON`, so I had to extract the insert string as a spring property, using "insert into table (jsonColumn) values (?::json)" for regular runs, and "insert into table (jsonColumn) values (? FORMAT JSON)" for the Spring "test" profile. – Suketu Bhuta Dec 29 '22 at 21:55
17

Passing the JSON as a String is the right approach, but as the error message tells you, you need to cast the parameter in the INSERT statement to a JSON value:

insert into the_table
   (.., evtjson, ..) 
values 
   (.., cast(? as json), ..)

Then you can use pStmt.setString(11, dtlRec.toString()) to pass the value

7

Most answers here defines ways of inserting into postgres json field with jdbc in a non-standard way, ie. it is db implementation specific. If you need to insert a java string into a postgres json field with pure jdbc and pure sql use:

preparedStatement.setObject(1, "{}", java.sql.Types.OTHER)

This will make the postgres jdbc driver (tested with org.postgresql:postgresql:42.2.19) convert the java string to the json type. It will also validate the string as being a valid json representation, something that various answers using implicit string casts does not do - resulting in the possibility of corrupt persisted json data.

aelgn
  • 821
  • 1
  • 11
  • 17
3

As others have mentioned, your SQL string needs to explicitly cast the bind value to the PostgreSQL json or jsonb type:

insert into t (id, j) values (?, ?::json)

Now you can bind the string value. Alternatively, you can use a library that can do it, for example jOOQ (works out of the box) or Hibernate (using a third party UserType registration). The benefits of this is that you don't have to think about this every time you bind such a variable (or read it). A jOOQ example:

ctx.insertInto(T)
   .columns(T.ID, T.J)
   .values(1, JSON.valueOf("[1, 2, 3]"))
   .execute();

Behind the scenes, the same cast as above is always generated, whenever you work with this JSON (or JSONB) data type.

(Disclaimer: I work for the company behind jOOQ)

Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
-1

if using spring boot: adding the following line to application.properties helped:

spring.datasource.hikari.data-source-properties.stringtype=unspecified

as Wero wrote:

This tells PostgreSQL that all text or varchar parameters are actually of unknown type

Uri Loya
  • 1,181
  • 2
  • 13
  • 34
-3

Instead of passing json object pass its string value and cast it to json in the query. Example:

JSONObject someJsonObject=..........

String yourJsonString = someJsonObject.toString();

String query = "INSERT INTO table (json_field) VALUES (to_json(yourJsonString::json))";

this worked for me.