PostgreSQL中数组与表的合并：实现ID数组与对应名称数组的关联查询

看起来你已经在处理分隔ID转数组的路上了，但原来的转换语句有点复杂，而且关联聚合的步骤没跟上。我来一步步帮你搞定这个需求：

首先，先简化你的ID数组转换逻辑——你原来的语句用了多层translate和substr，其实可以更简洁：因为你的分隔符是|（竖线前后带空格），直接替换成逗号再转数组就好：

string_to_array(replace(main.ids, ' | ', ','), ',') AS ids_array

接下来，要关联agents表拿到对应的名称，再聚合回数组/分隔字符串，我们可以用LATERAL unnest()来拆分数组，关联后再重新聚合。

方法1：输出数组格式的结果 #

WITH main_with_array AS (
    SELECT 
        ctid AS main_row_id, -- 用PostgreSQL内置行标识符分组，若main表有主键可替换为主键
        string_to_array(replace(ids, ' | ', ','), ',') AS ids_array,
        ids AS original_ids -- 保留原格式用于后续对照
    FROM main
)
SELECT
    m.ids_array AS ids,
    array_agg(a.names ORDER BY idx) AS names -- 严格保持ID与名称的顺序一致
FROM main_with_array m
LATERAL unnest(m.ids_array) WITH ORDINALITY AS u(id_val, idx)
LEFT JOIN agents a ON a.ids = u.id_val
GROUP BY m.main_row_id, m.ids_array
ORDER BY m.main_row_id;

方法2：输出竖线分隔的字符串格式 #

如果不需要数组，直接输出和原ids格式一致的竖线分隔字符串，把array_agg换成string_agg即可：

WITH main_with_array AS (
    SELECT 
        ctid AS main_row_id,
        string_to_array(replace(ids, ' | ', ','), ',') AS ids_array,
        ids AS original_ids
    FROM main
)
SELECT
    m.original_ids AS ids,
    string_agg(a.names, ' | ' ORDER BY idx) AS names
FROM main_with_array m
LATERAL unnest(m.ids_array) WITH ORDINALITY AS u(id_val, idx)
LEFT JOIN agents a ON a.ids = u.id_val
GROUP BY m.main_row_id, m.original_ids
ORDER BY m.main_row_id;

关键细节说明： #

• 使用WITH ORDINALITY和ORDER BY idx是为了保证名称的顺序和原ID串的顺序完全一致，避免聚合时顺序混乱。
• 用LEFT JOIN而不是INNER JOIN，可以防止某个ID在agents表中不存在时，整行数据丢失（此时对应的名称会是NULL，你可以根据需求用COALESCE(a.names, '未知')替换成默认值）。
• 如果你的main表有主键（比如id列），可以把ctid替换成主键，分组逻辑会更可靠。

测试用例验证 #

如果你需要验证，可以先创建测试表和数据：

-- 创建main表
CREATE TABLE main (ids TEXT);
INSERT INTO main VALUES 
('i1 | i2'),
('i2 | i3'),
('i3');

-- 创建agents表
CREATE TABLE agents (ids TEXT, names TEXT);
INSERT INTO agents VALUES 
('i1', 'agent1'),
('i2', 'agent2'),
('i3', 'agent3');

执行上面的查询语句，就能得到你期望的结果啦。

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