Zipping unnest on arrays from views

Before this release, zipping unnest functionality worked for arrays only in "tables" but did not support "views" as a source. This release improves that support further by accepting inputs from views as well.

UNNEST() on array columns

You can use UNNEST() on array columns in two ways. Using one of these two ways results in the items of the arrays being zipped together instead of joining.

ISO:SQL 2016 compliant syntax

SELECT a1.item, a2.item
FROM complextypes_arrays t, UNNEST(t.arr1, t.arr2) AS (a1, a2);

Postgres compatible syntax

SELECT UNNEST(arr1), UNNEST(arr2) FROM complextypes_arrays;

When unnesting multiple arrays with zipping unnest, the i'th item of one array will be placed next to the i'th item of the other arrays in the results. If the size of the arrays is not equal then the shorter arrays will be filled with NULL values up to the size of the longest array as shown in the following example:

The test data used in this example is arr1: {1, 2, 3}, arr2: {11, 12}

After running any of the queries listed in the examples, the result will be as shown here:


arr1 arr2
[1, 11]
[2, 12]
[3, NULL]

Example of an UNNEST() in an inline view using SELECT/FILTER of the inline view

In the following example the filter is not in the SELECT query that creates the inline view but a level above that.

SELECT id, ac1, ac2 FROM 
    (SELECT id, UNNEST(array_col1) AS ac1, UNNEST(array_col2) AS ac2 FROM some_view)
WHERE id <10;