Postgres JSONB存在对应数据但查询无结果的问题排查

以下是几种可能导致查询无结果的原因及对应解决方法：

• 数据类型不匹配
  如果payload里的id是数字类型而非字符串，直接用->>转字符串比较可能出现精度丢失（尤其是超大整数）。可以改用数字类型比较：

  SELECT "payload" FROM "tweets" WHERE ("payload"->'id')::bigint = 1596568061574008832;
  

  或者先检查id的实际类型：

  SELECT jsonb_typeof(payload->'id') FROM "tweets" LIMIT 1;
  

• 键名大小写敏感
  PostgreSQL的jsonb键名是大小写敏感的，确认payload里的键是小写id还是其他大小写形式（比如ID、Id）。可以用以下语句查看键名：

  SELECT jsonb_object_keys(payload) FROM "tweets" LIMIT 1;
  

• id不在顶层结构
  检查id是否嵌套在子对象中，比如payload->'user'->'id'。可以用以下语句查看完整的json结构：

  SELECT "payload" FROM "tweets" WHERE "payload"::text LIKE '%1596568061574008832%';
  

  找到id的实际路径后调整查询，比如：

  SELECT "payload" FROM "tweets" WHERE "payload"->'user'->>'id' = '1596568061574008832';
  

• 隐藏字符或空格
  如果id字符串前后有空格或不可见字符，直接匹配会失败。可以用trim函数处理后比较：

  SELECT "payload" FROM "tweets" WHERE trim(payload->>'id') = '1596568061574008832';
  

  或者查看id的原始长度和目标字符串长度对比：

  SELECT length(payload->>'id'), length('1596568061574008832') FROM "tweets" WHERE "payload"::text LIKE '%1596568061574008832%';
  

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