H2数据库中如何查询包含另一数组列所有值的数组列

问题场景 #

现有表A，包含foo、bar两个INT ARRAY类型列，需求是筛选出foo列包含对应行bar列所有元素的记录。

测试数据示例：

DROP TABLE A IF EXISTS;
CREATE TABLE A(foo INT ARRAY, bar INT ARRAY);
INSERT INTO A(foo, bar) VALUES
  (ARRAY[1, 2, 3], ARRAY[1, 2]), 
  (ARRAY[2, 3], ARRAY[1, 2, 3]);

期望查询结果仅返回foo值为[1, 2, 3]的行。

关于DISTINCT(foo || bar) = foo的误区 #

H2中的DISTINCT()、UNIQUE()等函数仅针对数组整体做唯一性判断，并非对数组内元素去重。foo || bar是将两个数组合并为一个新数组（比如ARRAY[1,2,3] || ARRAY[1,2]会得到ARRAY[1,2,3,1,2]），DISTINCT()作用于该合并数组时，只会判断数组本身是否唯一，无法实现“合并后去重元素集合等于原foo数组”的效果，因此这个思路不成立。

另外，H2对TABLE()、UNNEST()的原生支持有限，直接展开数组做元素级对比的常规方法会遇到兼容性问题。

可行解决方案 #

方案1：使用原生ARRAY_CONTAINS_ALL函数（H2 1.4.200+） #

H2 1.4.200及以上版本提供了ARRAY_CONTAINS_ALL函数，可直接判断第一个数组是否包含第二个数组的所有元素：

SELECT foo FROM A WHERE ARRAY_CONTAINS_ALL(foo, bar);

方案2：兼容低版本H2的子查询实现 #

如果使用的H2版本不支持ARRAY_CONTAINS_ALL，可以通过子查询结合UNNEST和ARRAY_CONTAINS实现：

SELECT a.foo
FROM A a
WHERE NOT EXISTS (
    SELECT 1
    FROM UNNEST(a.bar) AS b(val)
    WHERE NOT ARRAY_CONTAINS(a.foo, b.val)
);

逻辑说明：检查bar中的每个元素是否都被foo包含，只要有一个元素未被包含则排除该行。（低版本H2使用UNNEST时需开启PostgreSQL兼容模式，可通过连接参数MODE=PostgreSQL启用）

内容的提问来源于stack exchange，提问作者Marc Grue