You can 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.

Important: Changing an EXPLODE statement can result in an unintentional change to tamr_id values in the unified dataset. To avoid data loss and maintain the stability of tamr_id in mastering and categorization projects, as a best practice Tamr recommends that you explicitly set the tamr_id to a value you're sure is stable in the last transformation. See Managing Primary Keys.

Basic Example

Your data looks like this, with an array of strings in the Name attribute.

When you write the statement EXPLODE Name, each element of the Name array is 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.

Examples of these two forms follow, using the same initial data containing different types of empty and null values. A description is provided for each value so you can see how they are treated differently.

If you EXPLODE INNER Name, the result is:

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

Alternatively, if you write EXPLODE OUTER Name, the result is:

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.