On Mon, Mar 7, 2016 at 8:15 PM, Curt Huffman <curt.huff...@gmail.com> wrote:
> Thanks Rob & David! > > I got it to work using the following: > > > String qry = "INSERT INTO event " > + "(spotid, qid, userid, persid, ...., " > + "evtvalue, evtdt, evtjson) " > + "VALUES(?,?,?,?,?,?,?,?,?,?,to_json(?::json));"; > > This is redundant (though possibly the to_json become a no-op in this case, idk). Either cast (which is what you are doing when you say "?::json") so pass the text through the to_json function. What you are saying here to "please convert this json value I am handing you to....json". and > pStmt.setString (11,dtlRec.toString()); > > (another suggestion was to use: cast(? as json) which I haven't tried > yet.) > This has the benefit of being standard conforming, the "::" syntax is a PostgreSQL-ism. > This worked with an ultra-simple, 1-pair json object. {"New MbrID":34} > I'll try it with more complex structures next. > > Any opinions on using the postgres function, to_json, over the cast? > I'm reasonably certain there is no actual difference between the two so whatever syntax seems more natural. > However, from my (limited) understanding, I think I am now just incurring > additional processing overhead from all of this. > I think that I am stuffing text into a JSON object, then converting it > into a string for the preparedStatment, which then passes it to the JDBC > driver to re-convert it into a JSON object, and gets ultimately stored as a > text string in the column? Is that correct? > I suspect I'll have to reverse the process to read it back out, yes? > > Follow-up questions: > 1) Since I'm not (yet) using JSONB, but just regular JSON column, is there > much point to even using a JSON column? > Yes, you are using "text" as a serialization feature and by using a typed json column you are validating/constraining the text being sent to conform to JSON structure. Unless you have solid and overwhelming proof that using JSON is unacceptably slow you should use it from a "proper model" perspective. > 2) Will this method also work for JSONB column types? > This is how you do type conversion in PostgreSQL, there is nothing here (aside from the unnecessary to_json function call) that is json/jsonb specific. David J.