如何查询jsonb字符串字段值在指定数组中的ActiveRecord记录

问题场景 #

我需要从Dish模型的ActiveRecord集合中，筛选出params字段（jsonb类型）内的procart_id值属于给定数组的记录。

模型定义 #

create_table "dishes", force: :cascade do |t|
    ...
    t.jsonb     "params"
    ...
end

params字段结构 #

params字段的固定结构如下：

{"procart_id"=>"4", "procart_config"=>{}}

给定筛选数组 #

availabilities = ['4', '8', '11']

尝试过的代码及错误 #

我尝试了以下查询代码：

Dish.where("params::jsonb ->> 'procart_id' = any (array[?])", availabilities)

但触发了如下错误：

ActiveRecord::StatementInvalid: PG::InvalidTextRepresentation: ERROR:  invalid input syntax for type json
DETAIL:  The input string ended unexpectedly.
CONTEXT:  JSON data, line 1: 
: SELECT "dishes".* FROM "dishes" WHERE (params::jsonb ->> 'procart_id' = any (array['4', '8', '11']))

解决方案 #

正确写法1：直接使用ANY(?)传入数组 #

问题出在两点：一是params本身已经是jsonb类型，不需要额外用::jsonb强制转换；二是PostgreSQL的ANY语法可以直接接收ActiveRecord传入的数组参数，不需要手动构造array[]。修改后的代码：

Dish.where("params ->> 'procart_id' = ANY(?)", availabilities)

正确写法2：使用JSON包含查询（可选） #

如果你偏好更贴近JSON操作的方式，也可以用PostgreSQL的@>操作符，检查params是否包含指定的procart_id键值对：

Dish.where("params @> ?", availabilities.map { |id| { procart_id: id } }.to_json)

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