PostgreSQL查询含反斜杠转义JSON字段提取键值方法

你查询失败不是键名里带冒号的问题，核心原因是存储的metadata值是双重转义的字符串，不是可直接操作的原生JSON对象：

1. 你当前存的内容最外层包裹了一层双引号，内部的引号都用反斜杠转义，第一次JSON解析后得到的是一段普通文本，不是键值对结构的JSON对象，直接用JSON操作符取值必然失败
2. 你写的metadata->>'chat'本身逻辑也有误：数据里不存在chat这个顶层键，chat:title是完整的键名，中间的冒号是键名的组成部分，不是JSON的层级分隔符。

以PostgreSQL为例，先解包外层转义字符串、转成原生JSON类型后再取值即可：

-- 写法1：适用于metadata字段本身是json/jsonb类型的场景
SELECT (metadata #>> '{}')::jsonb ->> 'chat:title' AS chat_name
FROM 你的表名;

-- 写法2：适用于metadata字段是text/varchar字符串类型的场景
SELECT (trim(both '"' from metadata))::json ->> 'chat:title' AS chat_name
FROM 你的表名;

语句逻辑说明：

• #>> '{}' 或 trim(both '"' from metadata)：作用是去掉最外层包裹的双引号、解析转义符，拿到结构正确的JSON字符串
• ::json/::jsonb：把处理后的字符串强转为原生JSON类型，让JSON操作符可以正常工作
• ->> 'chat:title'：从JSON对象中提取键为chat:title的文本值，运行后会正确返回Random name Comunidad。

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