如何通过ActiveRecord查询jsonb字段键值对含null值的模型

问题背景 #

• 存在User模型，对应表包含jsonb类型字段email_triggers
• 字段存储结构示例：{"email_one": "3/1/22", "email_two": "4/9/22", "email_three": null}
• 目标：查询所有email_triggers字段中至少有一个键对应值为null的用户记录

原有代码错误分析 #

原有查询代码：

User.where("array[jsonb_each(email_triggers.value)] @> null")

生成SQL执行抛出PG::UndefinedTable错误，根因有两点：

1. 字段引用错误：email_triggers是users表的原生列，不存在email_triggers.value这种写法，PostgreSQL会将点号前的email_triggers识别为独立表名，因此抛出缺少FROM子句的错误
2. 函数用法与逻辑错误：jsonb_each是表值函数，返回的是行集合，不能直接放入数组构造器array[]中使用；同时SQL中任意值与null做@>包含运算时，返回结果永远是null（等价于不命中），判断逻辑本身不成立。

可用实现方案 #

方案1：JsonPath匹配（推荐，PostgreSQL 12+可用，性能最优） #

直接通过jsonb_path_exists函数遍历jsonb对象所有值判断null存在性，支持GIN索引优化查询速度：

User.where("jsonb_path_exists(email_triggers, '$.** ? (@ == null)')")

语法说明：$.**表示递归匹配当前jsonb对象下的所有层级值，? (@ == null)为筛选条件，只要存在任意值等于null即返回匹配命中。

方案2：子查询展开键值对（兼容所有PostgreSQL版本） #

低版本PostgreSQL不支持JsonPath时，可通过EXISTS子查询配合jsonb_each展开键值对判断：

User.where("EXISTS (
  SELECT 1 FROM jsonb_each(email_triggers) AS trigger_entry(key, value)
  WHERE value IS NULL
)")

逻辑说明：逐行遍历用户记录时，将当前记录的email_triggers字段展开为键值对行，只要存在任意行的value为null，EXISTS即返回true命中当前记录。

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