Re: Inserting JSON via Java PreparedStatment

On 03/07/2016 07:15 PM, Curt Huffman 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));";
>
> and
> pStmt.setString (11,dtlRec.toString());
>
> (another suggestion was to use: cast(?asjson) which I haven't tried yet.)
>
> 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?
>
> 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?
> 2) Will this method also work for JSONB column types?
>
> Finally, I humbly request a small addition to the postgres doco that
> illustrates this and the 'best' way to insert, manipulate, and retrieve
> JSON in postgres. Maybe even a small tutorial?

The below?:

http://www.postgresql.org/docs/9.5/interactive/datatype-json.html#JSON-KEYS-ELEMENTS

http://www.postgresql.org/docs/9.5/interactive/functions-json.html

>
> Thanks again!
> -Curt
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com