View Source

Unpacks array data into separate rows for each value. This transform operates on a single column.

This transform does not reference keys in the array. If your array data contains keys, use the unnest transform. See Unnest Transform.

flatten col: myArray

Output: Generates a separate row for each value in the array. Values of other columns in generated rows are copied from the source.

flatten: col: column_ref

Token	Required?	Data Type	Description
flatten	Y	transform	Name of the transform
col	Y	string	Source column name

col

Identifies the column to which to apply the transform. You can specify only one column.

Required?	Data Type
Yes	String (column name)

Example - Flatten an array

In this example, the source data includes an array of scores that need to broken out into separate rows.

Source:

LastName	FirstName	Scores
Adams	Allen	[81,87,83,79]
Burns	Bonnie	[98,94,92,85]
Cannon	Chris	[88,81,85,78]

Transform:

When the data is imported, you might have to re-type the Scores column as an array:

settype col: Scores type: 'Array'

You can now flatten the Scores column data into separate rows:

flatten col: Scores

Results:

LastName	FirstName	Scores
Adams	Allen	81
Adams	Allen	87
Adams	Allen	83
Adams	Allen	79
Burns	Bonnie	98
Burns	Bonnie	94
Burns	Bonnie	92
Burns	Bonnie	85
Cannon	Chris	88
Cannon	Chris	81
Cannon	Chris	85
Cannon	Chris	78

This example is extended below.

Example - Flatten and unnest together

While the above example nicely flattens out your data, there are two potential problems with the results:

There is no identifier for each test. For example, Allen Adams' score of 87 cannot be associated with the specific test on which he recorded the score.
There is no unique identifier for each row.

The following example addresses both of these issues. It also demonstrates differences between the unnest and the flatten transform, including how you use unnest to flatten array data based on specified keys.

For more information, see Unnest Transform.