sql - 按值数组搜索jsonb列

在我的Ruby on Rails项目中，我有一个SurveyResult模型，其作用域如下：

class SurveyResult < ActiveRecord::Base
  scope :with_purpose, -> (purpose) {
    where("raw #>> '{survey, purpose}' = ?", purpose)
  }
end

raw是一个jsonb列。此列中的数据由以下架构表示：

survey: { purpose: 'data' }

如果我试图获得目的是data的调查结果，则此范围工作正常：

SurveyResult.with_purpose('data')

但当我还想找到其他目的的调查结果时：

SurveyResult.with_purpose(['data', 'risk'])

它返回以下错误：

2.3.1 :042 > SurveyResult.with_purpose(['risk', 'data'])
  SurveyResult Load [dev] (1.5ms)  SELECT "survey_results".* FROM "survey_results" WHERE (raw #>> '{survey, purpose}' = 'risk','data')
ActiveRecord::StatementInvalid: PG::DatatypeMismatch: ERROR:  argument of WHERE must be type boolean, not type record
LINE 1: ...CT "survey_results".* FROM "survey_results" WHERE (raw #>> '...
                                                             ^
: SELECT "survey_results".* FROM "survey_results" WHERE (raw #>> '{survey, purpose}' = 'risk','data')

我怎样才能解决这个问题？

最佳答案

User.where("id = ?", 1)
Corresponding query to the database would look like,
SELECT "users".* FROM "users"  WHERE (id = 1)

User.where("id IN (?)", [1,2,3])
Corresponding query to the database would look like,
SELECT "users".* FROM "users"  WHERE (id IN (1,2,3))

"= ?" this operator expects on a single argument, so if you are passing an array to it an error would be thrown

ActiveRecord::StatementInvalid: PG::DatatypeMismatch: ERROR:  argument of WHERE must be type boolean, not type record

In order to pass an array you should modify your query to:

"IN (?)", This operator would work if you wanted to pass a single value to it or an array.

Both would work fine
User.where("id IN (?)", 1)
User.where("id IN (?)", [1,2,3])

关于sql - 按值数组搜索jsonb列，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/45692895/