Rails Active Record中如何使用IN操作符查询JSONB字段

查询无结果的原因 #

你误以为两次执行的SQL完全一致，实际上核心的jsonb取值运算符存在差异，这是查询无结果的根本原因：

• 可正常返回结果的查询用的是->>运算符：这是PostgreSQL jsonb类型的文本取值运算符，会把取出的json值转为普通varchar字符串，和IN子句里的字符串类型完全匹配，所以能正常查到结果。
• 传变量的查询用的是->运算符：这是jsonb类型的对象取值运算符，取出的结果是jsonb类型的字符串（本质是带双引号的json对象），和IN子句里的普通字符串类型不匹配，自然没有返回结果。

修复代码 #

只需要把传参写法里的->替换为->>即可正常返回结果：

CoursesProgram
.joins(:course)
.where(program_id: 12)
.where(
  "courses.lms_data->>'_id' IN (?)",
  ["604d26cadb61e238f542f2fa", "604541eb0ff9d8387b28828c"]
)

更符合PostgreSQL语法规范的写法可以用ANY运算符代替IN，避免数组为空时的语法错误：

CoursesProgram
.joins(:course)
.where(program_id: 12)
.where("courses.lms_data->>'_id' = ANY(?)", ["604d26cadb61e238f542f2fa", "604541eb0ff9d8387b28828c"])

两次查询触发的原因 #

两次查询的唯一差异是第二条带了LIMIT 11参数，该现象和你写的WHERE条件没有关联，是对返回的ActiveRecord Relation对象的后续操作触发的二次查询：
Rails的Relation是懒加载的，只有执行实际需要数据的操作才会触发SQL查询。常见的两次查询触发场景包括：

• 拿到Relation后先后执行了两次需要加载数据的操作，比如先调用to_a/present?加载全量数据，之后又调用了Kaminari/WillPaginate等分页组件的.page()方法（分页组件默认每页10条，会查询11条数据判断是否存在下一页）
• 在Rails控制台测试代码时，控制台默认打印查询结果的操作会触发一次额外查询

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