检查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) }

任何解决此问题的建议。

评论
  • 卟~怕
    卟~怕 回复

    One possible solution is to use jsonb_array_elements() to unroll the JSON arrays into sets. Then, in an EXISTS subquery, inner join these sets on common elements.

    SELECT *
       FROM some_table
       WHERE EXISTS (SELECT *
                            FROM jsonb_array_elements(some_table.foo->'bar') t (v)
                                 INNER JOIN jsonb_array_elements('[1,2]'::jsonb) s (v)
                                            ON t.v = s.v);

    db<>fiddle