PostgreSQL JSON 字段转 text 后带双引号，等值查询返回零行

JSON 列里明明有 "name": "Hello"，查询却匹配不到：

WHERE (payload -> 'name')::text = 'Hello'

把右侧改成 '"Hello"' 又能查到。这里的双引号来自 JSON 字符串的表示形式；-> 返回 JSON 值，再转成 text 得到的是它的 JSON 文本表示，不是解码后的字符串内容。

下面针对 PostgreSQL 当前受支持版本中的 json 或 jsonb 对象字段。目标是按字符串内容筛选，不是比较整段 JSON 文本，也不需要为此改表里的数据类型。

同时查看值与类型

可以在本机测试数据库运行以下只读示例：

SELECT payload -> 'name' AS json_value,
       (payload -> 'name')::text AS serialized_text,
       payload ->> 'name' AS field_text,
       pg_typeof(payload -> 'name') AS extracted_type,
       pg_typeof(payload ->> 'name') AS text_type
FROM (VALUES ('{"name":"Hello"}'::json)) AS v(payload);

前两列会保留 JSON 字符串的外层双引号，第三列是 Hello。后两列分别为 json 和 text。把输入改成 jsonb，-> 的返回类型对应变成 jsonb，->> 仍返回 text。

如果直接比较 json 与普通字符串，还可能先遇到 operator does not exist: json = unknown。给它加 ::text 能改变参与比较的类型，却没有把 JSON 字符串解码成业务文本；报错消失后返回零行，是需要继续核对的信号。

用 ->> 提取文本后比较

下面的完整查询不依赖业务表：

SELECT id
FROM (VALUES
    (1, '{"name":"Hello"}'::json),
    (2, '{"name":"World"}'::json)
) AS v(id, payload)
WHERE payload ->> 'name' = 'Hello';

预期只返回编号 1。接入实际查询时保留原有租户、时间和权限条件，仅替换错误的取值表达式。匹配值仍通过驱动参数绑定传入，不要把用户输入拼接到 SQL 字符串中。

嵌套对象可以先保留中间 JSON，再在最后一级取文本，例如 payload -> 'profile' ->> 'name'。过早使用 ->> 会把中间对象变成文本，后续就不能继续把它当作 JSON 对象逐层提取。

不要用 trim 删除外层双引号，或用 replace 删除所有双引号。真实值可能包含引号、反斜杠或换行；这些字符在 JSON 中需要转义，机械删字符不会完成 JSON 解码，反而会改坏内容。

保留缺失字段与字段类型的边界

没有对应键或结构不匹配时，提取操作会返回 SQL 空值。字段本身为 JSON null 时，->> 也返回 SQL 空值；后续普通等号比较不会把这些行选出来。若要区分两种情况，可以在保留 JSON 值的阶段检查类型：

SELECT id,
       json_typeof(payload -> 'name') AS value_type,
       payload ->> 'name' AS field_text
FROM (VALUES
    (1, '{}'::json),
    (2, '{"name":null}'::json),
    (3, '{"name":"null"}'::json)
) AS v(id, payload)
ORDER BY id;

第一行的 value_type 是 SQL 空值，第二行是文本 null，第三行是文本 string。第三行的 field_text 则是普通字符串 null。对 jsonb 使用对应的 jsonb_typeof。

->> 也能把数字等 JSON 值转成文本。因此，若业务严格只匹配 JSON 字符串，可以加上类型条件：

WHERE json_typeof(payload -> 'name') = 'string'
  AND payload ->> 'name' = 'Hello'

这段用于 json 列；jsonb 列需改用 jsonb_typeof。它明确了数据类型约定，不能自动修复字段里混入对象、数组或数字的问题。

对照结果与下游使用方式

验证时覆盖普通字符串、内含双引号的字符串、缺失键、JSON null、文字 "null" 以及数字与同内容字符串。结果应按业务值匹配，内部字符不应因修复而丢失。

查询表达式改变后，既有表达式索引是否适用也需要按实际执行计划判断，不能承诺改成 ->> 一定更快。先在只读查询中比较具体记录，再让依赖筛选结果的更新、删除或导出任务使用新条件。

保留原查询和返回样例以便回退。这里不需要清洗 JSON、删除引号或修改存储字段；如果修复后命中范围扩大，应先确认新增命中的记录符合预期，再恢复后续写操作。