Rails使用scope查询JSON hash字段内嵌套数组包含指定值的实现方法

示例 User 模型数据 #

[#<User
  id: 5,
  name: "Dave",
  email: "example@gmail.com",
  info:
   {"years_old"=>"30",
    "recent_purchases"=>["car", "house", "boat"]}>]

需求说明 #

User 模型的用户近期购买记录存储在 info 字段的 recent_purchases 数组内，且无法修改数据库结构，需要编写 Scope 实现两类查询：

• 查询 recent_purchases 包含 car 的用户
• 查询 recent_purchases 仅含有 car 的用户

无效写法参考 #

以下两种查询写法无法生效：

User.where("info->>'recent_purchases' IN (?)", 'car')
User.where("info->>'recent_purchases' = ?", ["car"])

可行实现方案 #

感谢 @Jacob 提供的可运行查询基础语句：

User.where("info -> 'recent_purchases' ? :car and jsonb_array_length(info -> 'recent_purchases') = 1", car: 'car')

1. 查询包含 car 的 Scope 定义 #

scope :where_cars_included, -> { where("info::jsonb -> 'recent_purchases' ? :purchase", purchase: 'car') }

说明：使用 PostgreSQL 原生的 ? 操作符判断 JSON 数组是否包含指定元素，先将 info 字段强转为 jsonb 类型保证操作符生效。

2. 查询仅含有 car 的 Scope 定义 #

scope :only_cars, -> { where("info::jsonb -> 'recent_purchases' ? :purchase and json_array_length(info -> 'recent_purchases') = 1", purchase: 'car') }

说明：在包含判断的基础上，增加 json_array_length 方法限制数组长度为1，保证数组内仅有 car 一个元素。

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