sql - 用另一个表中的相关值替换数组列中的值

Question

在我的数据库中，我有一个表 relations，其中有一列 relation_ids 包含用户 ID (user_id)。这采用具有许多可能 ID 的数组形式，例如:

{111,112,156,4465}

我有另一个表 names，其中包含有关用户的信息，例如 user_id、first_name、last_name 等。

我想创建一个 SQL 查询来返回 relations 中的所有行和所有列，但是将数组列 relation_ids 附加到 first_name来自 names 表的 ID。

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

Answer 1

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

我建议 ARRAY constructor在与 unnest() 和 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;

在任何情况下，此查询都会保留关系 中的所有 行。

需要注意的极端情况:
1. relation_ids 中的 NULL 值(对于整个列)被转换为空数组。 (与源中的空数组相同。)
2. NULL 元素 静默地从数组中删除。

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

db<> fiddle here

相关:

• LEFT OUTER JOIN on array column with multiple values
• PostgreSQL unnest() with element number

考虑规范化数据库设计:

• Can PostgreSQL array be optimized for join?