UNNEST vs EXPLODE Functions

Vertica provides two functions, UNNEST and EXPLODE to expand arrays into one or more rows. These functions offer the same functionality with subtle differences in syntax and output.

Let’s understand with a simple example.

CREATE TABLE orders (
        orderkey VARCHAR, 
        custkey INT,
        prodkey ARRAY[VARCHAR], 
        orderprices ARRAY [DECIMAL (12,2)],
        email_addrs ARRAY[VARCHAR]);

eonv2330=> select * from orders.
 orderkey | custkey |        prodkey         |         orderprices         |                  email_addrs
----------+---------+------------------------+-----------------------------+------------------------------------------------
 19626    |      91 | ["P1262","P68","P101"] | ["192.59","49.99","137.49"] | ["bob@example.com","robert.jones@example.com"]
 25646    |     716 | ["P997","P31","P101"]  | ["91.39","29.99","147.49"]  | ["bob2@example.com","robert@example.com"]
 25647    |     716 | ["P12"]                | ["8.99"]                    | ["jones@example.com"]
 19743    |     161 | ["P68","P101"]         | ["49.99","137.49"]          | ["alexjohnson@example.com"]
 19888    |     241 | ["P1262","P101"]       | ["197.59","142.49"]         | ["jonanthan@example.com"]

We have 3 array columns in the table. Let us expand 1 array column for a particular order key. Explode provides the position of the element in the array whereas unnest does not provide the position of element in the array. Unnest caccepts only array columns as input whereas explode accepts other columns as well as the input, for example, orderkey in the following example.

eonv2330=> SELECT orderkey, UNNEST (prodkey) FROM orders WHERE orderkey='19626';
 orderkey | value
----------+-------
 19626    | P1262
 19626    | P68
 19626    | P101
(3 rows)

eonv2330=> SELECT EXPLODE (orderkey, prodkey) over () FROM orders WHERE orderkey='19626';
 orderkey | position | value
----------+----------+-------
 19626    |        0 | P1262
 19626    |        1 | P68
 19626    |        2 | P101
(3 rows)

Now, let us expand 2 array columns. When expanding more than 1 array column, explode requires passing explode_count, number of arrays columns needed to be exploded whereas UNNEST by default will expand all columns passed into one or more rows

eonv2330=> SELECT EXPLODE (orderkey prodkey,orderprices USING PARAMETERS explode_count=2,skip_partitioning=true) FROM orders WHERE orderkey='19626';
+----------+-------------+-------------+-----------------+-----------------+
| orderkey | pos_prodkey | val_prodkey | pos_orderprices | val_orderprices |
+----------+-------------+-------------+-----------------+-----------------+
| 19626    |           0 | P1262       |               0 |          192.59 |
| 19626    |           0 | P1262       |               1 |           49.99 |
| 19626    |           0 | P1262       |               2 |          137.49 |
| 19626    |           1 | P68         |               0 |          192.59 |
| 19626    |           1 | P68         |               1 |           49.99 |
| 19626    |           1 | P68         |               2 |          137.49 |
| 19626    |           2 | P101        |               0 |          192.59 |
| 19626    |           2 | P101        |               1 |           49.99 |
| 19626    |           2 | P101        |               2 |          137.49 |
+----------+-------------+-------------+-----------------+-----------------+
(9 rows)

eonv2330=> SELECT orderkey,unnest (prodkey,orderprices) FROM orders WHERE orderkey='19626';
+----------+-------------+-----------------+
| orderkey | val_prodkey | val_orderprices |
+----------+-------------+-----------------+
| 19626    | P1262       |          192.59 |
| 19626    | P1262       |           49.99 |
| 19626    | P1262       |          137.49 |
| 19626    | P68         |          192.59 |
| 19626    | P68         |           49.99 |
| 19626    | P68         |          137.49 |
| 19626    | P101        |          192.59 |
| 19626    | P101        |           49.99 |
| 19626    | P101        |          137.49 |
+----------+-------------+-----------------+
(9 rows)