Create structured arrays

Sigma supports structured arrays that are aware of the data type of their contents. This allows for accurate type checking and lets you use values from an array without converting their data type.

About structured arrays

When Sigma retrieves data from your connected data platform, it uses a supported Sigma data type for the data. Sigma treats all semi-structured data, including arrays and JSON data, as the variant data type. Variant is a flexible format that allows Sigma to handle deeply nested data regardless of its structure or contents. Because of this flexibility, Sigma doesn’t know the data type of an array’s contents, even when every value in the array has the same data type.

Consider an array whose values all share a data type, like the following:

Array(1, 2, 3)

The following sections illustrate how Sigma handles this array’s contents with and without structured arrays.

Without structured arrays

When Sigma treats this array and its contents as variant data, common operations require extra steps. For example, the following formula compares the first item in an array of numbers to another number:

Array(1, 2, 3)[0] = 1

Even though the left-hand side of the = returns 1, this formula doesn’t return True, and is instead invalid. It’s invalid because, although Array(1, 2, 3)[0] resolves to 1, it retains the variant data type, and variant data can’t be compared to a number using the = sign. To resolve this, you can convert the variant data to a number with the Number function:

Number(Array(1, 2, 3)[0]) = 1

This returns True, because both the value and data type are equivalent. However, having to perform this additional type conversion is unintuitive.

With structured arrays

When Sigma treats this array as a structured array, the contents of the array retain their data type. For example, consider the same formula from the previous section that compares the first item in an array of numbers to another number:

Array(1, 2, 3)[0] = 1

This formula now returns True as expected, because the values 1 and 1 are the same, and the item selected from the array retained its data type. You no longer have to convert to the number data type to perform the comparison.

Create a structured array

Sigma creates a structured array when you use any of the following functions with data of the same data type:

• Array
• ArrayAgg
• ArrayAggDistinct

Sigma treats arrays from other sources as variant data. This includes any native array columns in your data platform, as well as arrays returned by other Sigma functions, such as SplitToArray and ArraySlice.

If an array contains values of the same data type along with Null data, Sigma treats the resulting array as a structured array of the shared data type. For example, the following returns an array of numbers:

Array(1, Null, 2)

Use the Array function

The Array function creates a structured array from the values you specify. Sigma determines the data type of the array’s contents from those values:

When the values in an array created with the Array function have different data types, each value you access from the array is variant data.

For example, the following formula returns 1 as variant data:

Array(1, "Bob")[0]

And the following formula returns "Bob" as variant data:

Array(1, "Bob")[1]

Use the ArrayAgg or ArrayAggDistinct function

The ArrayAgg and ArrayAggDistinct functions aggregate the values of a column into a structured array. Because the values come from a single column, the contents of the array always have the data type of that column.

For example, if the Fruit column contains text data, the following formula returns a structured array of text:

ArrayAgg([Fruit])

The following formula returns the first fruit from the resulting array as text data:

ArrayAgg([Fruit])[0]