在 PostgreSQL 中，如何将数组中的每个元素用作另一个表中的键

发布于03月13日

存在如下SQL查询:

问题A:发生了什么事？

SELECT
    user,
    color_id_array
FROM
    relation;

结果A:

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

查询B:

SELECT
    inf_name,
    inf_code
FROM
    infomation;

结果B:

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

结果A中的COLOR_ID_ARRAY是一个允许重复元素的array.如何创建查询以获得以下输出？正在使用的数据库是PostgreSQL.

请注意，情况并非如此.

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"]

推荐答案

您可以取消ID数组的嵌套(同时跟踪它们的原始位置)，使用连接带来信息表，然后再次聚合:

select r.*, array_agg(i.inf_name order by rx.color_seq) color_name_array
from relation r
cross join lateral unnest(r.color_id_array) with ordinality rx(color_code, color_seq)
inner join information i on i.inf_code = rx.color_code
group by r.user

group by子句应该包含relation表的主键(我假设是user).