检查JSONB列中的数组是否包含另一个数组中的任何值

由 发布于 postgresqlruby-on-railsactiverecord
 收藏

问题

Find all records in sample data where foo->bar include at least one item from given array e.g. [1,2]

样本数据

Record 1 => 'foo': {
  'bar': [1,2]
}

Record 2 => 'foo': {
  'bar': [3,4]
}

Record 3 => 'foo': {
  'bar': [5,7]
}

Record 4 => 'foo': {
  'bar': [1]
}

Record 5 => 'foo': {
  'bar': [2,3]
}

预期结果

Record 1 => 'foo': {
  'bar': [1,2]
}


Record 4 => 'foo': {
  'bar': [1]
}

Record 5 => 'foo': {
  'bar': [2,3]
}

I tried using operators @> and ?|, the first checks against JSOB and returns only if all items are included. Second has issues with types JSOB => Integer[]

的SQL

SELECT  "some_table".* FROM "some_table" WHERE (foo->'bar' @> '[1,2]'::jsonb);

滑轨范围

scope :for_bar, -> (bars) { where("foo->'bar' @> ?::jsonb", bars.to_json) }

任何解决此问题的建议。