如何将ARRAY类型字段与COLUMN字段进行表关联查询？

示例表结构 #

Table_A（单ID列） #

CREATE TABLE Table_A (
    ID INT,
    Info_A VARCHAR(50)
);

-- 示例数据
INSERT INTO Table_A VALUES
(1, 'Info_A_1'),
(2, 'Info_A_2'),
(3, 'Info_A_3');

Table_B（ID数组列） #

CREATE TABLE Table_B (
    IDs INT[],
    Info_B VARCHAR(50)
);

-- 示例数据
INSERT INTO Table_B VALUES
(ARRAY[1,2], 'Info_B_1'),
(ARRAY[2,3], 'Info_B_2'),
(ARRAY[4], 'Info_B_3');

正确关联SQL #

SELECT 
    a.ID,
    a.Info_A,
    b.Info_B
FROM Table_A a
INNER JOIN (
    SELECT 
        unnest(IDs) AS ID,
        Info_B
    FROM Table_B
) b ON a.ID = b.ID;

逻辑说明 #

• 先用unnest(IDs)将Table_B的数组字段拆分为多行记录，每个数组元素对应一条独立数据，同时保留原表的Info_B字段
• 再将拆分后的临时表与Table_A通过ID字段做内连接，即可匹配到所有数组中包含对应ID的关联记录

期望输出结果 #

ID | Info_A   | Info_B
---|----------|---------
1  | Info_A_1 | Info_B_1
2  | Info_A_2 | Info_B_1
2  | Info_A_2 | Info_B_2
3  | Info_A_3 | Info_B_2

关于你之前尝试的问题 #

你之前用inner join结合unnest和union all未成功，大概率是误用了union all——此场景不需要合并多个结果集，仅需先展开数组再关联，多余的union all会打乱关联逻辑。

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