Array Types

To be recognized as an array, a source column must contain values that are:

• Bracketed by square brackets
• Values in cell are delimited by commas

Such columns are likely to be recognized as Array data type.

The following are valid arrays:

[1,2,3]
["A","B"]
["C",["D","E"],"F",["G",["H","I"]]]

• Ragged arrays: If the number of elements varies between two arrays, they are considered ragged. In the above, all three arrays have a different number of top-level elements (3,2,4).
• Nested arrays: When an array element is an array itself, the element is considered a nested array. See the last example above.

For more information, see Array Data Type. 

Create Arrays

Within 

Create by extraction

You can create an array of values by extracting pattern-based values from a specified column. The following transformation extracts from the msg column a list of all values where all letters are capitalized and places them into the new acronyms column:

Notes:

• An empty input column value renders an empty array.

• In the final row, the 

  D s lang
  item pattern

   matches on the "Y" value. To fix this, you can change the Pattern matching value to the following, which matches on two or more uppercase letters in a row:

  Code Block
  `{upper}{upper}+`

Create by nesting

You can create arrays by nesting together the values from multiple columns.

Source:

You want to nest the values in num1 and num2 into a single array and then to nest the array with num3:

Then, you can perform the nesting of the top-level elements:

In the generated columns, you notice that all values are quoted, even though these values are integers.

You can use the following transformation to remove the quotes from the nest2 column:

After removing the unused nest1 column, the data looks like the following:

Create from column values

You can use one of several available functions to create arrays from a column's values. 

Source:

The following transformation generates a new column in which each row contains an array of all of the values of the input column:

Results:

Notes:

• The second parameter on the LIST function defines the maximum number of values to write. 1000 is the default.
• All values in the generated array are written as String values.
• Quoted values are escaped in the output.

The following functions allow you to generate various types of arrays from a column's set of values.

Create from Object type

You can extract the keys of an Object column into an array of string values. In an Object type, the values are listed in quoted key/value pairs and can be nested. See Object Data Type.

Source:

Suppose your Object data looks like the following:

You can run the following transformation to extract the top-level keys into arrays in a new named column:

Results:

For more information, see KEYS Function.

Read from Arrays

You can read values from arrays in your dataset. 

Compute from Arrays

You can use the following functions to perform computations on the values in your arrays:

Combine Arrays

You can combine arrays together using a variety of methods of combining.

Source:

The following transformation concatenates the above arrays into a single single array:

Results:

These functions can be used to combine arrays together:

Break out Arrays

Expand arrays into rows

You can break out arrays into individual values using the following transformations. Here is some example data from the nest2 column that was generated earlier. The num3 column is retained for reference:

You can use the following simple transformation to flatten the values in nest2 into individual values in each row:

Results:

Unnest array elements into columns

You can break out individual elements of an array into separate columns.

 Source:

The following transform retrieves the second and third elements of each array:

This one retrieves the first element of the array that is nested as the second element of the array:

The resulting data should look like the following: