在 postgresql 和 json 解析中通过 jackson 使用文本类型列转义双引号
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11310763/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
escaping of double quote with text type column at postgresql and json parsing via Hymanson
提问by Yair Zaslavsky
I have the following table defined at postgresql:
我在 postgresql 中定义了下表：
Column | Type | Modifiers
-----------------+------------------------+-----------
id | uuid | not null
entity_snapshot | text |
Indexes:
"pk_id" PRIMARY KEY, btree (id)
I would like to store the following JSon string:
我想存储以下 JSon 字符串：
[ "org.test.MyUniqueId", {: "uuid" : "f4b40050-9716-4346-bf84-8325fadd9876": } ]
for some testing, instead of doing this using Hymanson, I try to type an SQL manually, and here is my problem - I can't seem to get ti right.
My current attempt is:
对于某些测试，我没有使用 Hymanson 来执行此操作，而是尝试手动键入 SQL，这是我的问题 - 我似乎无法正确输入。
我目前的尝试是：
insert into my_table(id,entity_snapshot) values ('d11d944e-6e78-11e1-aae1-52540015fc3f','[ \"org.test.MyUniqueId\", {: \"uuid\" : \"f4b40050-9716-4346-bf84-8325fadd9876\": } ]');
Procuedure a record that looks like what I need when I select from the table, but when I try to use Hymanson to parse it I get an error -
当我从表中选择时，生成一个看起来像我需要的记录，但是当我尝试使用 Hymanson 解析它时，我收到一个错误 -
org.apache.commons.lang.SerializationException: org.codehaus.Hymanson.JsonParseException: Unexpected character (':' (code 58)): was expecting double-quote to start field name
Needless to say that if the same record is inserted via my java code , I can get it parsed and when it comes to looking at the record with human eyes, it looks the same to me.
Can you tell me where I am wrong in my SQL insert statement?
不用说，如果通过我的 java 代码插入相同的记录，我可以解析它，当涉及到用人眼查看记录时，它对我来说看起来是一样的。
你能告诉我我的 SQL 插入语句哪里错了吗？
回答by corsair
You can use Dollar-Quoted String Constants. Details are here Postgres documentation
您可以使用美元引用的字符串常量。详细信息在这里Postgres 文档
In your case query should look like
在你的情况下，查询应该看起来像
insert into my_table(id,entity_snapshot) values ('d11d944e-6e78-11e1-aae1-52540015fc3f',$$[ "org.test.MyUniqueId", {: "uuid" : "f4b40050-9716-4346-bf84-8325fadd9876": } ]$$);
回答by a_horse_with_no_name
Double quotes inside single quotes do not need any escaping.
单引号内的双引号不需要任何转义。
insert into my_table (id,entity_snapshot)
values
(
'd11d944e-6e78-11e1-aae1-52540015fc3f',
'["org.test.MyUniqueId", {: "uuid" : "f4b40050-9716-4346-bf84-8325fadd9876": }]'
);
will work just fine:
会工作得很好：
postgres=> create table my_table (id uuid, entity_snapshot text);
CREATE TABLE
Time: 34,936 ms
postgres=> insert into my_table (id,entity_snapshot)
postgres-> values
postgres-> (
postgres(> 'd11d944e-6e78-11e1-aae1-52540015fc3f',
postgres(> '["org.test.MyUniqueId", {: "uuid" : "f4b40050-9716-4346-bf84-8325fadd9876": }]'
postgres(> );
INSERT 0 1
Time: 18,255 ms
postgres=>
