postgresql - 如何检查和选择 postgresql jsonb 列与特定数组重叠的行

Question

例如，我有一个包含 jsonb 列的表，没有嵌套，就像 [1,2,3] 一样，如何选择与某个数组重叠(具有相同元素)的行，比如 [1,2,6,8,3]?

我试过

select * from mytable
where TRANSLATE(myjsonb::jsonb::text, '[]','{}')::INT[]  && ARRAY[1,2,6,8,3]
limit 100

但它不起作用。我是 postgresql 的新手。谢谢

Answer 1

您可以按如下方式进行判断:

postgres=# select array(select jsonb_array_elements_text('[1, 2, 3]'::jsonb)) as arr;
   arr   
---------
 {1,2,3}
(1 row)

postgres=# select array(select jsonb_array_elements_text('[1, 2, 3]'::jsonb))::int[] && '{1,2,4}'::int[] as bool;
 bool 
------
 t
(1 row)

postgres=# select array(select jsonb_array_elements_text('[1, 2, 3]'::jsonb))::int[] && '{5,6,7}'::int[] as bool;
 bool 
------
 f
(1 row)

更清晰的例子:

postgres=# \d my_jsonb
  Table "public.my_jsonb"
 Column | Type  | Modifiers 
--------+-------+-----------
 data   | jsonb | 

postgres=# select * from my_jsonb ;
      data       
-----------------
 [1, 2, 3]
 [2, 1, 6, 7, 8]
 [2, 1, 2]
 [3, 4, 5]
 [3, 8, 5, 9]
(5 rows)

postgres=# select
    array(select jsonb_array_elements_text(data))::int[] as arr,
    array(select jsonb_array_elements_text(data))::int[] && '{1,2,4}'::int[] as bool 
from 
    my_jsonb;
     arr     | bool 
-------------+------
 {1,2,3}     | t
 {2,1,6,7,8} | t
 {2,1,2}     | t
 {3,4,5}     | t
 {3,8,5,9}   | f
(5 rows)