带有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屋！