Rails（postgres）使用jsonb数组进行查询

Question

我的产品模型有一个jsonb字段specs（我们使用ActiveRecord的store_accessor进行管理）。我的许多产品规格在哈希表中都有一个叫做spec_options的规格。

到现在为止，这个spec_option字段只是文本。现在它需要成为一个数组。

以前用于查询此字段产品的范围是：

scope :with_spec_options, ->(spec_options) { 
    where("'#{spec_options}'::jsonb \? (specs->>'spec_option')") 
}

Ruby等价代码（只是为了帮助理解这段代码在做什么）：

select{ |product| spec_options.include?(product.specs['spec_option']) }

如果 spec_option 是一个常规列，那么它的 ActiveRecord 等效代码如下：

where(spec_option: spec_options)

然而，现在specs['spec_options']是一个数组，我无法这样做。我想我需要使用Postgres的?|jsonb运算符，但我无法弄清楚如何将此操作的右侧转换为正确的格式。

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

有人有想法吗？

- Obversity

Answer 1

你需要使用@>运算符，它可以检测左侧值是否包含右侧值。 "包含"适用于对象和数组，因此以下查询有效：

SELECT * FROM products WHERE specs->'spec_options' @> '["spec1", "spec2"]';

我相信你可以将其转换为与ActiveRecord兼容的语法，就像这样：

scope :with_spec_options, ->(spec_options) { 
  where("specs->'spec_option' @> ?", spec_options.to_json) 
}

- Robert Nubel

---

谢谢！那就差不多了。操作的两边都需要是jsonb，所以这个可以用：where("specs->'spec_option' @> ?::jsonb", spec_options.to_json) - Obversity

放弃那个::jsonb转换，你不需要它：where("specs->'spec_option' @> ?", spec_options.to_json) - Obversity

唉，我以为这是一个解决方案，但它并不完全符合我的需求。它需要在任何查询选项匹配时返回结果。而这个答案只会返回所有匹配的结果。 - Obversity

@Obversity: 试试这个：where("specs->'spec_option' ?| :options", options: spec_options)。 - Robert Nubel

抱歉，我的意思是：where("specs->'spec_option' ?| array[:options]", options: spec_options)。将其插入正确的Postgres语法中有点棘手。 - Robert Nubel

太好了。谢谢！ - Obversity