sql array 数组基本用法(三)

展平数组

SELECT *
FROM UNNEST(['foo', 'bar', 'baz', 'qux', 'corge', 'garply', 'waldo', 'fred'])
  AS element
WITH OFFSET AS offset
ORDER BY offset;

element	offset
foo	0
bar	1
baz	2
qux	3
corge	4
garply	5
waldo	6
fred	7

如需展平一整列 ARRAY，同时保留每行中其他列的值，请使用 CROSS JOIN 将带有 ARRAY 列的表联接到该 ARRAY 列的 UNNEST 输出。这是一种相互关联的交叉联接：UNNEST 运算符引用了源表中每一行的 ARRAY 列，该列之前曾出现在 FROM 子句中。对于源表中的每一行 N，UNNEST 将行 N 的 ARRAY 展平成一组包含 ARRAY 元素的行，然后 CROSS JOIN 将这组新行与源表的单行 N 联接起来。

WITH sequences AS
  (SELECT 1 AS id, [0, 1, 1, 2, 3, 5] AS some_numbers
   UNION ALL SELECT 2 AS id, [2, 4, 8, 16, 32] AS some_numbers
   UNION ALL SELECT 3 AS id, [5, 10] AS some_numbers)
SELECT id, flattened_numbers
FROM sequences
CROSS JOIN UNNEST(sequences.some_numbers) AS flattened_numbers;

id	flattened_numbers
1	0
1	1
1	1
1	2
1	3
1	5
2	2
2	4
2	8
2	16
2	32
3	5
3	10