Re: Error inserting JSON string to Postgres DB

Got it working. Thanks again for your help Felix!
3 things I had to do:
   
   - Use TWO double quotes for every double quote, so {"name": "whatever"} becomes {""name"": ""whatever""}   

   - Put the final JSON string in double quotes when making the insertion request. Single quotes don't work!   

   - In the JDBC request for insert, specify the data type as varchar instead of text even though text is the data type for the column I am inserting to. Otherwise, the inert will throw "invalid data type: text" error.   


So as in your example, the following should be supplied for the insert: "{""name"": ""whatever""}"
Jun
    On Saturday, December 2, 2023 at 08:49:39 AM EST, Felix Schumacher <[email protected]> wrote:  
 
  
Hi Jun,
 
I have attached a working minimal example, that uses a JSON string and inserts that with a prepared statement.
 
It assumes a locally running postgres which can be changed of course.
 
The interesting part is:
 
* You have to escape comma and quotes in the JSON string.
   Given a JSON object like {"name": "whatever"} you will have to add quotes to mask the comma and quotes to mask the quotes.
   Your string will have to look like  "{""name"": ""whatever""}"
 
* You have to cast the parameter to json in your insert statement.
   Add a ::json to the placeholder, as in "insert into something values (?::json)"
 
Regards
 
 Felix
 
 Am 02.12.23 um 14:19 schrieb Jun Zhuang:
  
 
 Hi Filex, 
  Thanks for the response. I tried with the single quotes and singles quotes + ::json, still got the same error. 
  Just to be sure, are you using a Postgres DB? Also one thing I forgot to mentioned in my original email was the field I am trying to insert into is of data type text instead of varchar, I wonder if that makes any difference? 
  Following are some screenshots of what I am trying to do: 
  
 
  
 
 
 
 
 
 
   
 On Saturday, December 2, 2023 at 05:58:52 AM EST, Felix Schumacher <[email protected]> wrote: 
 
 
 
 
 
 
 Hi Jun,
 
 I am not sure, what you tried already. When I place the following text into a JDBC Sampler's query field, I get no errors:
 
 create table something (id int, data json);
 insert into something values (1, '{"name": "whatever"}'::json);
 
 Same result, when I remove the casting and use
 
 create table something (id int, data json);
 insert into something values (1, '{"name": "whatever"}');
 
 Can you show us a minimal example that fails for you?
 
 
 
 
 
 Regards
 
  Felix
 
 
 
 
 Am 01.12.23 um 23:24 schrieb Jun Zhuang:
 
 
 >  Hi,
 > I am getting Cannot have quote-char in plain field:['{"] error when trying to insert a JSON string into a Postgres DB table using JDBC request.
 > I tried the following but nothing worked:   
 >    - place the JSON string in single quotes, 
 >    - escaping the double quotes with \", 
 >    - placing the string in $$<JSON string here>$$ but nothing worked. 
 > There is no issue with the connection and other select/insert queries and I was able to insert from pgAdmin with the same string in single quotes though.
 > The JSON string looks like this: {"A":"A", "B":[{"C":"C"}]}
 > I am really out of ideas at this time. Can someone help?
 > Thanks,Jun
 > 
 > 
 
  
  ---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]