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

Question

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

{'name': 'Grzegorz', 'age': 123} can be a valid javascript value, but not a valid JSON. Blindly converting all ' to " could be dangerous, just consider f.ex. {'name': 'Gregor "Grzegorz"', 'age': 123} (if that's a possible generated value, because that's too could be parsed with javascript). Instead, I would look into how these values are generated & search for a method to generate valid JSON values. — pozs
– pozs, Commented Nov 25, 2015 at 14:33

klin · Accepted Answer · 2015-11-25 15:05:04Z

5

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)

edited Nov 25, 2015 at 15:05

answered Nov 25, 2015 at 14:34

klin

123k15 gold badges241 silver badges263 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related