SQL 将数组列转换为数组中具有旧键的行

Question

我在 PostgreSQL 中有一个表，有两列:id 和 nodes。 id 的类型为 bigint，因为它是表中的主键。 nodes 是一个bigint 数组。我想创建一个新表，其中 node 数组中的节点是键，唯一的其他列是与该 对应的 ids 数组节点。

假设原始表格是这样的:

id | nodes
1  | {200, 300, 400}
2  | {200, 300, 400}

那么新表应该是这样的:

node | ids
200  | {1, 2}
300  | {1, 2}
400  | {1, 2}

Answer 1

demo: db<>fiddle

SELECT node, array_agg(id) as ids
FROM (
    SELECT id, unnest(nodes) as node 
    FROM nodes
)s 
GROUP BY node
ORDER BY node

unnest() 将数组扩展为每个元素一行。

然后，您可以按数组元素进行GROUP，并将ids与array_agg聚合

---

在您的情况下，这可以缩短为没有任何子查询或连接的查询:

SELECT unnest(nodes) as node, array_agg(id) as ids 
FROM nodes
GROUP BY node
ORDER BY node