H2数据库TEXT ARRAY字段使用LIKE通配符查询方法

由于H2的已知问题，直接在EXISTS子查询中使用UNNEST引用外层表的数组字段会触发报错，以下两种方案可实现需求：

方案一：CROSS JOIN UNNEST + DISTINCT #

通过交叉连接展开数组，筛选匹配元素后去重，保证每行仅返回一次：

SELECT DISTINCT w.*
FROM app.worklist w
CROSS JOIN UNNEST(w.profession) AS p
WHERE p LIKE 'A%';

方案二：遍历数组索引（无重复行） #

利用H2内置的SYSTEM_RANGE生成数组的索引范围，结合ARRAY_GET逐个检查元素，无需去重：

SELECT *
FROM app.worklist w
WHERE EXISTS (
    SELECT 1
    FROM SYSTEM_RANGE(1, ARRAY_LENGTH(w.profession)) AS idx
    WHERE ARRAY_GET(w.profession, idx.value) LIKE 'A%'
);

方案对比 #

• 方案一代码简洁直观，但数组中多个元素匹配时会返回重复行，需依赖DISTINCT去重；
• 方案二避免了重复结果，性能更优，适合数组元素较多或对结果唯一性要求高的场景。

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