Database Reference
In-Depth Information
Unnesting Arrays to Rows
A common function used with arrays is
unnest
, which allows you to expand the ele‐
ments of an array into a set of rows, as demonstrated in
Example 5-15
.
Example 5-15. Expanding array with unnest
SELECT
unnest
(
'{XOX,OXO,XOX}'
::
char
(
3
)[])
As
tic_tac_toe
;
tic_tac_toe
---
XOX
OXO
XOX
Although you can add multiple unnests to a single
SELECT
, if the number of resultant
rows from each array is not balanced, you get some head-scratching results.
A balanced unnest, as shown in
Example 5-16
, gives you three rows, as you would often
want.
Example 5-16. Unnesting balanced arrays
SELECT
unnest
(
'{three,blind,mice}'
::
text
[])
As
t
,
unnest
(
'{1,2,3}'
::
smallint
[])
As
i
;
t |i
------+-
three |1
blind |2
mice |3
If you remove an element of one array so that you don't have an equal number of elements
in both, you get the result shown in
Example 5-17
.
Example 5-17. Unnesting unbalanced arrays
SELECT
unnest
(
'{blind,mouse}'
::
varchar
[])
As
v
,
unnest
(
'{1,2,3}'
::
smallint
[])
As
i
;
v |i
------+-
blind |1
mouse |2
blind |3
mouse |1
blind |2
mouse |3
Version 9.4 introduces a multiargument
unnest
function that puts in null placeholders
where the arrays are not balanced. The main drawback with the new
unnest
is that it
