Rails+PostgreSQL：编写ActiveRecord Scope筛选特定JSON列记录

针对 Answer 表的 document JSON 列，我们需要编写名为 untouched 的 Scope，筛选出以下三类记录：

• document 为空 JSON 对象（{}）
• document 仅包含 name 键
• document 仅包含 secondary_name 键

分数据库实现方案 #

不同数据库对 JSON 操作的语法差异较大，以下是 PostgreSQL 和 MySQL 环境下的实现：

PostgreSQL（推荐使用 jsonb 类型）

在 Answer 模型中添加 Scope：

class Answer < ApplicationRecord
  scope :untouched, -> {
    where(
      "(document = '{}'::jsonb) OR 
       (document ? 'name' AND array_length(jsonb_object_keys(document), 1) = 1) OR 
       (document ? 'secondary_name' AND array_length(jsonb_object_keys(document), 1) = 1)"
    )
  }
end

• document ? 'name'：检查 document 是否包含 name 键
• array_length(jsonb_object_keys(document), 1) = 1：确保 document 只有一个键
• 三个条件通过 OR 连接，覆盖所有目标场景

MySQL

MySQL 使用 JSON 原生函数实现逻辑：

class Answer < ApplicationRecord
  scope :untouched, -> {
    where(
      "(document = '{}') OR 
       (JSON_CONTAINS_PATH(document, 'one', '$.name') AND JSON_LENGTH(document) = 1) OR 
       (JSON_CONTAINS_PATH(document, 'one', '$.secondary_name') AND JSON_LENGTH(document) = 1)"
    )
  }
end

• JSON_CONTAINS_PATH(document, 'one', '$.name')：检查是否存在 name 键（'one' 表示至少存在一个匹配路径）
• JSON_LENGTH(document) = 1：限制 document 仅含一个键

验证效果 #

对应示例中的记录：

a1 = Answer.create(document: {})
a2 = Answer.create(document: { name: "anything" })
a3 = Answer.create(document: { secondary_name: "anything" })
a4 = Answer.create(document: { other_important_key: "other_important_data" })

Answer.untouched.pluck(:id) # 返回 [a1.id, a2.id, a3.id]，符合预期

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