Search code examples
postgresqljsonb

Insert json string with field names enclosed in single quotes into postgresql as a jsonb field

I have strings representing jsons where field names and values are enclosed in single quotes, for example {'name': 'Grzegorz', 'age': 123}. Let's assume that I have also a table in postgres database:

CREATE TABLE item (
     metadata jsonb
);

I'm trying to insert rows using JOOQ. JOOQ generates the following statement: insert into Item values('{''name'': ''Grzegorz'', ''age'': 123}'::jsonb); but an error is thrown:

ERROR:  invalid input syntax for type json
LINE 1: insert into Item values('{''name'': ''Grzegorz'', ''age'': 1...
Token "'" is invalid.
JSON data, line 1: {'...

Is there any possibility to insert json with names enclosed in single quotes ' instead of double quotes " or should I convert all ' to "?

Thanks in advance!

Grzegorz

Solution

  • Json syntax requires double quotes, so It is not a question of Postgres. Server accepts only valid json values.

    You can use replace():

    insert into item 
values(replace('{''name'': ''Grzegorz'', ''age'': 123}', '''', '"')::jsonb);

select * from item;

             metadata             
----------------------------------
 {"age": 123, "name": "Grzegorz"}
(1 row)