如何在PostgreSQL的JSONB字段中插入含内部双引号且无转义反斜杠的JSON数据

嘿，我来帮你把这个问题理清楚～

首先得明确一个核心点：你想要的{"name": "maddy", "about": "saying "something" is always good"}格式其实是不符合JSON规范的。JSON语法要求字符串内部的双引号必须用反斜杠转义，否则这个JSON会因为语法错误无法被解析，PostgreSQL也不会接受这样的无效数据。所以存储的合法JSON必须是带转义的格式，这是前提。

为什么查询时会看到转义符？ #

当你执行select * from test时，PostgreSQL是把JSONB类型转换成JSON的字符串表示形式输出，这时候会显示符合规范的转义符。但实际上，JSONB类型内部存储的是已经解析好的结构，转义符只是字符串表示时的需要——当你提取字段的实际文本内容时，转义符会被自动处理掉。

解决方案建议 #

1. 正确提取字符串内容（推荐）

如果你只是想获取about字段的实际文本（不带转义符），可以用->>操作符或者jsonb_extract_path_text函数来提取：

-- 用->>操作符提取文本
select exif->>'about' from test;

-- 或者用函数
select jsonb_extract_path_text(exif, 'about') from test;

执行后会返回：saying "something" is always good，这正是你想要的原始字符串内容。

2. 更优雅的插入方式

你之前用反斜杠转义的方式是可行的，但还有更方便的写法，比如用美元引号避免手动转义：

insert into test values ($${"name": "maddy", "about": "saying \"something\" is always good"}$$);

或者直接用jsonb_build_object函数，完全不需要手动转义双引号，PostgreSQL会自动帮你生成合法的JSONB：

insert into test values (jsonb_build_object(
    'name', 'maddy',
    'about', 'saying "something" is always good'
));

3. 不推荐：生成无效JSON格式

如果你非要得到看起来没有内部转义的字符串（注意：这会生成无效JSON，后续操作可能出错），可以用字符串替换，但强烈不建议这么做：

select replace(exif::text, '\"', '"') from test;

总结 #

合法的JSON必须保留内部双引号的转义，这是语法要求。但你完全不需要担心这个转义符影响实际使用——当你提取具体的字符串内容时，PostgreSQL会自动解析掉转义符，给你原始的文本。尽量不要生成无效的JSON格式，否则后续的JSON解析、操作都会出现问题。

内容的提问来源于stack exchange，提问作者Suganesh Kumar