将数组列中的值替换为另一个表中的相关值

在我的数据库中我有一个表relations有一个柱子relation_ids包含用户的 ID（user_id）。它采用具有多个可能 ID 的数组形式，例如：

{111,112,156,4465}

我还有另一张桌子names包含有关用户的信息，例如user_id, first_name, last_name etc.

我想创建一个 SQL 查询来返回所有行relations包含所有列，但附加数组列relation_ids with first_name来自names表替换了 ID。

是否可以作为某种子查询？

假设您想要保留数组中的顺序 - 名字的列出顺序与原始 ID 的顺序相同relation_ids.

我建议一个数组构造函数在相关子查询上unnest() and WITH ORDINALITY，加入到names表，如：

SELECT r.*
     , ARRAY (
          SELECT n.first_name
          FROM   unnest(r.relation_ids) WITH ORDINALITY AS a(user_id, ord)
          JOIN   names n ON n.user_id = a.user_id
          ORDER  BY a.ord
          )  AS first_names
FROM   relations r;

此查询保留all行来自relations任何状况之下。

需要注意的极端情况：

1. A NULL值在relation_ids（对于整列）被转换为空数组。 （与源中的空数组相同。）
2. NULL elements会默默地从数组中删除。

如果这些极端情况是可能的，您可能想要定义所需的行为......

fiddle

Related:

• 具有多个值的数组列上的 LEFT OUTER JOIN
• PostgreSQL unnest() 与元素编号

考虑标准化的数据库设计：

• PostgreSQL 数组可以针对连接进行优化吗？