EXPLODE

Expands one or more one-dimensional array columns into individual rows, one row per element, with any other columns specified in the query. For each exploded array, the results include two columns, one for the array element index, and one for the value at that position. If the function explodes a single array, these columns are named position and value by default. If the function explodes two or more arrays, the columns for each array are named pos_column-name and val_column-name. You can use an AS clause in the SELECT to change these column names.

This function requires an OVER() clause.

Behavior Type

Immutable

Syntax

EXPLODE (column[,...] [USING PARAMETERS explode_count=number_of_arrays_to_explode]) 
OVER ( [window-partition-clause] )

Arguments

column Column in the table being queried. You must specify at least as many array columns as the value of the explode_count parameter. Columns that are not arrays are passed through without modification.

Parameters

explode_count The number of array columns to explode (default 1). The function checks each column, up to this value, and either explodes it if is an array or passes it through if it is not an array or if this limit has been reached. If the value of explode_count is greater than the number of array columns specified, the function returns an error.

Supported Data Types

Columns to be exploded are restricted to 1D arrays of any primitive type.

Other columns may be of any type.

Null-Handling

This function expands each element in an array into a row, including nulls.

Examples

The following examples illustrate using EXPLODE() with the OVER(PARTITION BEST) clause.

Consider an orders table with columns for order keys, customer keys, product keys, order prices, and email addresses, with some containing arrays. A basic query in Vertica results in the following:

=> SELECT orderkey, custkey, prodkey, orderprices, email_addrs FROM orders LIMIT 5;
  orderkey  | custkey |                    prodkey                    |            orderprices            |                                                  email_addrs
------------+---------+-----------------------------------------------+-----------------------------------+----------------------------------------------------------------------------------------------------------------
 113-341987 |  342799 | ["MG-7190 ","VA-4028 ","EH-1247 ","MS-7018 "] | ["60.00","67.00","22.00","14.99"] | ["bob@example,com","robert.jones@example.com"]
 111-952000 |  342845 | ["ID-2586 ","IC-9010 ","MH-2401 ","JC-1905 "] | ["22.00","35.00",null,"12.00"]    | ["br92@cs.example.edu"]
 111-345634 |  342536 | ["RS-0731 ","SJ-2021 "]                       | ["50.00",null]                    | [null]
 113-965086 |  342176 | ["GW-1808 "]                                  | ["108.00"]                        | ["joe.smith@example.com"]
 111-335121 |  342321 | ["TF-3556 "]                                  | ["50.00"]                         | ["789123@example-isp.com","alexjohnson@example.com","monica@eng.example.com","sara@johnson.example.name",null]
(5 rows)

This example expands the orderprices column for a specified customer, in ascending order. The custkey and email_addrs columns are repeated for each array element.

=> SELECT EXPLODE(orderprices, custkey, email_addrs) OVER(PARTITION BEST) AS (position, orderprices, custkey, email_addrs)
   FROM orders WHERE custkey='342845' ORDER BY orderprices;
 position | orderprices | custkey |         email_addrs
----------+-------------+---------+------------------------------
        2 |             |  342845 | ["br92@cs.example.edu",null]
        3 |       12.00 |  342845 | ["br92@cs.example.edu",null]
        0 |       22.00 |  342845 | ["br92@cs.example.edu",null]
        1 |       35.00 |  342845 | ["br92@cs.example.edu",null]
(4 rows)

When you explode an array column that contains null values, the null values are displayed as empty.

You can explode more than one array column by specifying the explode_count parameter.

> SELECT EXPLODE(orderkey, prodkey, orderprices USING PARAMETERS explode_count=2)
OVER(PARTITION BEST)
AS (orderkey,pk_idx,pk_val,ord_idx,ord_val)
FROM orders
WHERE orderkey='113-341987';
  orderkey  | pk_idx |  pk_val  | ord_idx | ord_val
------------+--------+----------+---------+---------
 113-341987 |      0 | MG-7190  |       0 |   60.00
 113-341987 |      0 | MG-7190  |       1 |   67.00
 113-341987 |      0 | MG-7190  |       2 |   22.00
 113-341987 |      0 | MG-7190  |       3 |   14.99
 113-341987 |      1 | VA-4028  |       0 |   60.00
 113-341987 |      1 | VA-4028  |       1 |   67.00
 113-341987 |      1 | VA-4028  |       2 |   22.00
 113-341987 |      1 | VA-4028  |       3 |   14.99
 113-341987 |      2 | EH-1247  |       0 |   60.00
 113-341987 |      2 | EH-1247  |       1 |   67.00
 113-341987 |      2 | EH-1247  |       2 |   22.00
 113-341987 |      2 | EH-1247  |       3 |   14.99
 113-341987 |      3 | MS-7018  |       0 |   60.00
 113-341987 |      3 | MS-7018  |       1 |   67.00
 113-341987 |      3 | MS-7018  |       2 |   22.00
 113-341987 |      3 | MS-7018  |       3 |   14.99
(16 rows)

You might store data of a primitive type in a set, a collection of unordered, unique elements. To explode a set column, you must explicitly cast the set column as an array column, or you receive an error. The following example explodes the email_addrs set column for a specified customer:

=> SELECT EXPLODE(email_addrs::ARRAY[VARCHAR], custkey) OVER(PARTITION BEST) AS (position, email_addrs, custkey)
   FROM orders WHERE custkey='342321';	
 position |        email_addrs        | custkey
----------+---------------------------+---------
        0 | 789123@example-isp.com    |  342321
        1 | alexjohnson@example.com   |  342321
        2 | monica@eng.example.com    |  342321
        3 | sara@johnson.example.name |  342321
        4 |                           |  342321
(5 rows)