使用 jsonb 数组的 Rails (postgres) 查询 [英] Rails (postgres) query with jsonb array
问题描述
我的产品模型有一个 jsonb 字段 specs
(我们使用 ActiveRecord 的 store_accessor
对其进行管理).我的许多产品的规格都有一个名为 spec_options
的散列规格.
My Product model has a jsonb field specs
(which we're managing using ActiveRecord's store_accessor
). Many of my products' specs have a spec in that hash called spec_options
.
在此之前,这个 spec_option
字段只是文本.现在它需要是一个数组.
Before now, this spec_option
field was just text. Now it needs to be an array.
之前用于查询此字段的产品的范围是:
The scope used before now to query products for this field was this:
scope :with_spec_options, ->(spec_options) {
where("'#{spec_options}'::jsonb ? (specs->>'spec_option')")
}
Ruby 等价物(只是为了帮助理解它在做什么):
Ruby equivalent (just to help understand what this is doing):
select{ |product| spec_options.include?(product.specs['spec_option']) }
ActiveRecord 等效项(如果 spec_option
是常规列):
ActiveRecord equivalent (if spec_option
were a regular column):
where(spec_option: spec_options)
然而,既然 specs['spec_options']
是一个数组,我不能那样做.我想我需要使用 postgres 的 ?|
jsonb 操作符,但我不知道如何将这个操作的右边变成正确的格式.
However, now that specs['spec_options']
is an array, I can't do that. I think I need to use postgres' ?|
jsonb operator, but I can't work out how to get the right side of this operation into the correct format.
Ruby 等价物:
def self.with_spec_options(spec_options)
all.select{|product|
if product.specs['spec_options'].present?
product.specs['spec_options'].any?{|option|
spec_options.include?(option)
}
else
false
end
}
end
有人有想法吗?
推荐答案
你想要使用的是 @>
运算符,用于测试您的左侧值是否包含右侧值.包含"适用于对象和数组,因此以下查询将适用:
What you want to use is the @>
operator, which tests whether your left-hand value contains the right-hand value. "Contains" works for both objects and arrays, so the following query would work:
SELECT * FROM products WHERE specs->'spec_options' @> '["spec1", "spec2"]';
我相信您可以像这样转换为与 ActiveRecord 兼容的语法:
Which I believe you can transform into ActiveRecord-compatible syntax like so:
scope :with_spec_options, ->(spec_options) {
where("specs->'spec_option' @> ?", spec_options.to_json)
}
这篇关于使用 jsonb 数组的 Rails (postgres) 查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!