Re: Inserting JSON via Java PreparedStatment

On Mon, Mar 7, 2016 at 8:15 PM, Curt Huffman <curt(dot)huffman(at)gmail(dot)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.