PostgreSQL：如何将数组元素作为另一表键生成对应名称数组？

需求场景 #

现有两张表：

1. relation表存储用户与对应颜色ID数组（允许元素重复）
2. infomation表存储颜色编码与对应名称

需要将relation表中每个用户的颜色ID数组，逐一映射为infomation表中的颜色名称，生成与原数组长度、顺序、重复情况完全一致的名称数组。

原表数据与查询 #

1. relation表查询及结果

SELECT
    "user",
    color_id_array
FROM
    relation;

查询结果：

user, color_id_array
john, [1, 2]
bob, [2, 2, 2]
amy, [2, 2, 3]

2. infomation表查询及结果

SELECT
    inf_name,
    inf_code
FROM
    infomation;

查询结果：

inf_code, inf_name
1, red
2, green
3, blue

实现SQL #

SELECT
    r."user",
    r.color_id_array,
    array_agg(i.inf_name ORDER BY pos) AS color_name_array
FROM
    relation r
LEFT JOIN LATERAL unnest(r.color_id_array) WITH ORDINALITY AS arr(id, pos)
    ON true
LEFT JOIN infomation i
    ON arr.id = i.inf_code
GROUP BY
    r."user", r.color_id_array
ORDER BY
    r."user";

逻辑说明 #

• 拆分数组并保留顺序：使用unnest(r.color_id_array) WITH ORDINALITY将数组拆分为单行元素，同时记录每个元素在原数组中的位置（pos），确保后续聚合时顺序与原数组一致。
• 关联映射名称：通过LEFT JOIN关联infomation表，将每个颜色ID替换为对应的名称。
• 聚合回数组：使用array_agg(i.inf_name ORDER BY pos)按原位置重新聚合为数组，完全匹配原数组的长度、重复元素及顺序。

期望输出 #

user, color_id_array, color_name_array
john, [1, 2], ["red", "green"]
bob, [2, 2, 2], ["green", "green", "green"]
amy, [2, 2, 3], ["green", "green", "blue"]

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