Postgres中jsonb键存在性校验结果异常求助

核心原因：Postgres与MySQL的JSON键大小写敏感性差异 #

Postgres的jsonb类型对键名是严格大小写敏感的，而MySQL的JSON_EXTRACT默认（多数配置下）是大小写不敏感的。如果你的poll字段中实际存储的键是Ended、ENDED或带空格的 ended这类与ended大小写/格式不一致的名称，就会出现你遇到的异常：

• poll ? 'ended'匹配不到实际存在的键，所以无结果
• NOT(poll ? 'ended')会返回所有不匹配ended的行（包括那些键名不一致但实际存在类似键的行）
• poll->'ended'返回<null>是因为键名不匹配，Postgres会返回null而非不存在的键

验证步骤 #

1. 查看poll字段的实际内容，确认键名：

   SELECT id, poll FROM joi_post LIMIT 5;
   

2. 提取所有行的poll键名，检查是否存在拼写/大小写差异：

   SELECT id, jsonb_object_keys(poll) AS poll_keys FROM joi_post;
   

解决办法 #

1. 匹配实际键名：如果验证后发现键名是Ended这类，直接修改查询语句：

   -- 查询存在'Ended'键的行
   SELECT id, poll FROM joi_post WHERE poll ? 'Ended';
   -- 查询不存在的行
   SELECT id, poll FROM joi_post WHERE NOT(poll ? 'Ended');
   

2. 大小写不敏感查询：如果需要忽略大小写匹配，用jsonb_object_keys配合ILIKE：

   -- 查询存在任意大小写形式'ended'键的行
   SELECT id, poll FROM joi_post 
   WHERE EXISTS (
     SELECT 1 FROM jsonb_object_keys(poll) AS k 
     WHERE k ILIKE 'ended'
   );
   -- 查询不存在的行
   SELECT id, poll FROM joi_post 
   WHERE NOT EXISTS (
     SELECT 1 FROM jsonb_object_keys(poll) AS k 
     WHERE k ILIKE 'ended'
   );
   

3. 确认字段类型：确保poll确实是jsonb类型，而非text或json（虽然json也支持?操作符，但jsonb性能更好）：

   SELECT column_name, data_type FROM information_schema.columns 
   WHERE table_name = 'joi_post' AND column_name = 'poll';
   

   如果是text类型，查询时需要先转换：poll::jsonb ? 'ended'

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