Explode

You may choose to EXPLODE an attribute if you want a separate row for each value in an array.

The template for EXPLODE is
EXPLODE attribute; or EXPLODE expression AS attribute;

You can only EXPLODE attributes or expressions which are arrays. If an attribute or expression contains nested arrays, you can EXPLODE the attribute multiple times. Each statement, however, can only include one EXPLODE at a time.

Basic Example

Let's say our data looks like this, with an array of strings in the Name attribute.

You could write the statement EXPLODE Name and each element of the Name array will be separated into its own row:

INNER and OUTER

There are two forms of EXPLODE:

• EXPLODE INNER drops the corresponding row when the attribute or expression is an empty array or null

• EXPLODE OUTER preserves empty arrays and nulls, generating a null in the exploded row instead.

If you do not specify INNER or OUTER, EXPLODE is INNER by default.

Let's explore these two forms. We'll start with initial data containing different types of empty and nulls, which we describe in a third column so you can see how they are treated differently.

Our initial data looks like this:

If we EXPLODE INNER Name, the result will be:

Notice how the rows for CO and KY disappeared since INNER drops a row if it is an empty array or null. Any nulls or empty values in an array, however, received their own row. You can see this behavior for CA, MI, and TX, these values were each given their own row.

Alternatively, if we had written EXPLODE OUTER Name, the result will be:

Here you can see the null value for CO is generated by a new null, and a new null is also generated for the empty array in KY.