PostgreSQL中如何将ID数组映射为对应值数组？

嘿，这个需求在PostgreSQL里其实很好实现，咱们可以借助内置的数组函数和关联操作来搞定，我给你具体的实现步骤和代码：

核心思路 #

要完成ID数组到值数组的转换，关键是先把数组拆分为单行数据，关联映射表拿到对应值，再重新聚合为数组，并且要保证数组元素的顺序和原ID数组一致。

具体实现 #

首先假设你的两张表分别是：

• 映射表：map_table（字段id、value）
• 存储ID数组的表：id_array_table（字段id_arr）

基础查询语句 #

SELECT 
  i.id_arr,
  array_agg(m.value ORDER BY idx) AS value_arr
FROM id_array_table i
-- 把ID数组拆成带顺序索引的行
JOIN LATERAL unnest(i.id_arr) WITH ORDINALITY AS u(id_val, idx) ON true
-- 关联映射表拿到对应value
JOIN map_table m ON m.id = u.id_val
-- 按原ID数组分组，重新聚合为值数组
GROUP BY i.id_arr;

关键细节解释 #

• unnest(i.id_arr) WITH ORDINALITY：这个函数会把数组的每个元素拆成一行，同时带上元素在原数组中的位置索引（idx），这样后续聚合时可以保持原数组的顺序，避免元素乱序。
• array_agg(m.value ORDER BY idx)：把关联得到的value按原数组的索引顺序重新聚合为数组，完美匹配你要的结果格式。

处理ID数组中存在无效ID的情况 #

如果你的ID数组里可能有不存在于映射表的ID，可以用LEFT JOIN代替JOIN，还能通过COALESCE给无效ID设置默认值：

SELECT 
  i.id_arr,
  -- 把不存在的ID对应的value替换为0，你也可以改成其他默认值
  array_agg(COALESCE(m.value, 0) ORDER BY idx) AS value_arr
FROM id_array_table i
JOIN LATERAL unnest(i.id_arr) WITH ORDINALITY AS u(id_val, idx) ON true
LEFT JOIN map_table m ON m.id = u.id_val
GROUP BY i.id_arr;

测试示例 #

你可以用下面的代码创建测试数据，验证查询效果：

-- 创建并插入映射表数据
CREATE TABLE map_table (
  id INT PRIMARY KEY,
  value INT
);
INSERT INTO map_table VALUES (1,4), (2,5), (3,7);

-- 创建并插入ID数组表数据
CREATE TABLE id_array_table (
  id_arr INT[]
);
INSERT INTO id_array_table VALUES ('{1,2}'), ('{1,3}'), ('{1,2,3}');

运行基础查询语句后，就能得到你给出的示例结果：

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