问题
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) }
任何解决此问题的建议。
One possible solution is to use
jsonb_array_elements()
to unroll the JSON arrays into sets. Then, in anEXISTS
subquery, inner join these sets on common elements.db<>fiddle