如何在不使用ANY()且数组转文本时实现PostgreSQL表关联？

场景说明 #

现有两个PostgreSQL表，结构及数据如下：

-- Table 1
id1  bool1
A    t
B    f
A    f
B    t   

-- Table 2（bool2已从布尔数组转为文本列）
id2  bool2
A    {"t", "f"}
B    {"f"}
A    {"t", "f"}
B    {"t"}   

需要实现两表关联，关联条件为id1=id2且bool1的值存在于bool2文本数组的元素中，且不能使用ANY()函数。

解决方案 #

方法1：字符串位置匹配

利用POSITION()函数判断bool1的带引号字符串是否存在于bool2文本中，确保匹配完整的数组元素：

SELECT *
FROM table1
LEFT JOIN table2 
  ON id1 = id2 
  AND POSITION('"' || bool1 || '"' IN bool2) > 0;

方法2：正则表达式匹配

通过正则匹配精准定位带引号的布尔值，避免部分匹配的风险：

SELECT *
FROM table1
LEFT JOIN table2 
  ON id1 = id2 
  AND bool2 ~ ('"' || bool1 || '"');

方法3：拆分文本数组为行后关联

如果系统支持字符串转数组及拆分行的函数，可以先将bool2的文本数组拆分为单个布尔值，再进行关联，逻辑更清晰：

SELECT t1.*, t2.*
FROM table1 t1
LEFT JOIN table2 t2
  ON t1.id1 = t2.id2
  AND EXISTS (
    SELECT 1
    FROM unnest(string_to_array(replace(replace(t2.bool2, '{', ''), '}', ''), ', ')) AS arr_val
    WHERE (arr_val = 't')::boolean = t1.bool1
  );

注：replace(replace(t2.bool2, '{', ''), '}', '')用于去除文本数组的大括号，string_to_array将文本转为数组，unnest将数组拆分为单行数据，最后通过EXISTS判断是否存在匹配的布尔值。

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