sql - 展平与数组的关系以针对每个数组条目发出一行

Question

给定一个这样定义的表:

CREATE TABLE test_values(name TEXT, values INTEGER[]);

...以及以下值:

| name  | values  |
+-------+---------+
| hello | {1,2,3} |
| world | {4,5,6} |

我正在尝试查找将返回的查询:

| name  | value |
+-------+-------+
| hello | 1     |
| hello | 2     |
| hello | 3     |
| world | 4     |
| world | 5     |
| world | 6     |

我已经查看了 the upstream documentation on accessing arrays , 并尝试考虑使用 the unnest() function 的解决方案看起来像，但一直空着。

理想的解决方案即使在除了要扩展的数组之外还有大量列且没有主键的情况下也应该易于使用。处理具有多个阵列的情况并不重要。

Answer 1

我们可以把set-returning函数放在unnest()进入 SELECT 列表 like Raphaël suggests .这曾经在 Postgres 10 之前出现极端情况问题。请参阅:

• What is the expected behaviour for multiple set-returning functions in SELECT clause?

从 Postgres 9.3 开始，我们还可以使用 LATERAL为此加入。将返回集合的函数放入 FROM 列表而不是 SELECT 列表是一种更简洁、符合标准的方法:

SELECT name, value
FROM   tbl, unnest(values) value;  -- implicit CROSS JOIN LATERAL

一个细微的区别:由于 unnest() 返回 no row，这会从结果中删除具有空/NULL values 的行，而相同的在 FROM 列表中转换为 NULL 值并返回。 100% 等效查询是:

SELECT t.name, v.value
FROM   tbl t
LEFT   JOIN unnest(t.values) v(value) ON true;

参见:

• What is the difference between LATERAL JOIN and a subquery in PostgreSQL?