如何查询Postgres中存储的数组列(数组交集)

Question

我有一个表，其中包含数组中的值，如下所示。

id | contents_id
1 | [1, 3, 5]
2 | [1, 2]
3 | [3, 4, 6]
4 | [2, 5]

如何编写查询数组，例如[1, 2]这样它检查数组的值而不是整个数组？

如果找到数组的任何公共值，则获取所有元组。

如果查询[1, 2]，则必须从上表中获取id=>1, 2, 4，因为它包含1或2。

Answer 1

考虑使用 intarray 扩展。它提供了一个 && 运算符来测试整数数组重叠。这是一个小提琴，举个例子。

select id from test where ARRAY[1,2] && contents_id;

虽然您可以使用运算符查询它，但我认为最好使用整数 ID 制作一个连接表。

Answer 2

在 1-D int 数组&&运算符

上，arrayoverlap

是最快的，正如@LaposhasúAcsa建议的那样。因此，仅当arrayoverlap不可用或想要使用一维整数数组以外的任何东西时，我的答案才成立。

检查UNNESThttps://www.postgresql.org/docs/current/static/functions-array.html

CREATE TABLE t45407507 (
id SERIAL PRIMARY KEY 
,c int[]
);
insert into t45407507 ( c) values
(ARRAY[1,3,5])
, (ARRAY[1,2])
, (ARRAY[3,4,6])
, (ARRAY[2,5]);
select DISTINCT id from 
(SELECT id,unnest(c) as c
from t45407507) x 
where x.c in (1,2);

可以通过LATERAL连接缩短

select DISTINCT id from
t45407507 x,unnest(c) ec
where ec in (1,2);

FROM子句中的逗号 (，( 是CROSS JOIN的缩写表示法。 对于表函数(如unnest()(自动假定LATERAL。

重写WHERE以使用ARRAY作为参数

SELECT DISTINCT id FROM
t45407507 x,unnest(c) ec
WHERE ec = ANY(ARRAY[1,2]);