Arrays

This section covers querying and manipulating arrays in Firebolt.

Declaring ARRAY data types in table definitions

Array types are declared using ARRAY(<type>) where <type> can be any data type that Firebolt supports. This includes the ARRAY data type, so arrays can be arbitrarily nested. If you load an array from a CSV file, the arrays in the CSV file must be enclosed in double quotes (""). For example, if a CSV file contains a row containing value1 , value2 , "[array_value3 , array_value4]", you can create a table using the following code to read array_value3 and array_value4 into array_values.

CREATE EXTERNAL TABLE IF NOT EXISTS array_example
    (
      value1 STRING,
      value2 STRING,
      array_values ARRAY(TEXT)
    )
    URL = 's3://path_to_your_data/'
    OBJECT_PATTERN = '*'
    TYPE = (csv);

Array literals are also supported. For example, the SELECT statement shown below is valid.

SELECT [1,2,3,4]

Basis for examples

All examples in this topic are based on the players table from the example database. The platforms column is of type ARRAY(TEXT).

SELECT playerid, nickname, platforms FROM players ORDER BY playerid LIMIT 5

playerid int null	nickname text null	platforms array(text null) null
1	steven70	[‘Nintendo’, ‘PC’]
2	burchdenise	[‘PC’, ‘Nintendo’, ‘iOS’, ‘Nintendo’]
3	stephanie86	[‘Nintendo’, ‘PC’]
4	sabrina21	[‘iOS’, ‘PC’, ‘PlayStation’, ‘PlayStation’]
5	kennethpark	[‘Xbox’, ‘PlayStation’, ‘iOS’, ‘Nintendo’, ‘Xbox’]

Rows: 5Execution time: 14.26ms

Comparing values against arrays

You can compare a scalar value against the elements of an array using the ANY/ALL (array) quantified comparison operators. ANY returns TRUE if the comparison holds for at least one element, and ALL returns TRUE if it holds for every element.

SELECT 2 = ANY(ARRAY[1, 2, 3]) AS any_match,
       5 > ALL(ARRAY[1, 2, 3]) AS all_match;

any_match boolean null	all_match boolean null
True	True

Rows: 1Execution time: 8.31ms

Simple array functions

There are several fundamental functions that you can use to work with arrays, including ARRAY_LENGTH, ARRAY_CONCAT, and ARRAY_FLATTEN. See the respective reference for a full description. Brief examples are shown below.

LENGTH example

LENGTH returns the number of elements in an array.

SELECT playerid, nickname, LENGTH(platforms) AS num_platforms
FROM players
ORDER BY playerid LIMIT 5

playerid int null	nickname text null	num_platforms int null
1	steven70	2
2	burchdenise	4
3	stephanie86	2
4	sabrina21	4
5	kennethpark	5

Rows: 5Execution time: 15.38ms

ARRAY_CONCAT example

ARRAY_CONCAT combines multiple arrays into a single array.

SELECT playerid, ARRAY_CONCAT(platforms, ['Android', 'Web']) AS all_platforms
FROM players
ORDER BY playerid LIMIT 3

playerid int null	all_platforms array(text null) null
1	[‘Nintendo’, ‘PC’, ‘Android’, ‘Web’]
2	[‘PC’, ‘Nintendo’, ‘iOS’, ‘Nintendo’, ‘Android’, ‘Web’]
3	[‘Nintendo’, ‘PC’, ‘Android’, ‘Web’]

Rows: 3Execution time: 11.37ms

ARRAY_FLATTEN example

ARRAY_FLATTEN converts an ARRAY of ARRAYs into a single flattened ARRAY. Note that this operation flattens only one level of the array hierarchy.

SELECT ARRAY_FLATTEN([ [[1,2,3],[4,5]], [[2]] ]) AS flattened_array;

flattened_array array(array(int))
[[1, 2, 3], [4, 5], [2]]

Rows: 1Execution time: 7.58ms

Manipulating arrays with Lambda functions

Firebolt Lambda functions are a powerful tool that you can use on arrays to extract results. Lambda functions iteratively perform an operation on each element of one or more arrays. Arrays and the operation to perform are specified as arguments to the Lambda function.

Lambda function general syntax

The general syntax pattern of a Lambda function is shown below. For detailed syntax and examples, see the reference topics for Lambda functions.

<LAMBDA_FUNC>(<arr1_var>[, <arr2_var>][, ...<arrN_var>] -> <operation>, <array1>[, <array2>][, ...<arrayN>])

Parameter	Description
`<LAMBDA_FUNC>`	Any array function that accepts a Lambda expression as an argument. For a list, see Lambda functions.
`<arr1_var>[, <arr2_var>][, ...<arrN_var>]`	A list of one or more variables that you specify. The list is specified in the same order and must be the same length as the list of array expressions (`<array1>[, <array2>][, ...<arrayN>]`). At runtime, each variable contains an element of the corresponding array. The specified `<operation>` is performed for each variable.
`<operation>`	The operation that is performed for each element of the array. This is typically a function or Boolean expression.
`<array1>[, <array2>][, ...<arrayN>]`	A comma-separated list of expressions, each of which evaluates to an `ARRAY` data type.

Optionally, you can enclose the variables in parentheses:

<LAMBDA_FUNC>((<arr1_var>[, <arr2_var>][, ...<arrN_var>]) -> <operation>, <array1>[, <array2>][, ...<arrayN>])

Lambda function example: single array

Consider the following TRANSFORM array function that uses a single array variable and reference in the Lambda expression. This example applies the UPPER function to each element p in the ARRAY-typed column platforms. This converts each element in each platforms array to upper-case.

SELECT playerid, TRANSFORM(p -> UPPER(p), platforms) AS upper_platforms
FROM players
ORDER BY playerid LIMIT 5

playerid int null	upper_platforms array(text null) null
1	[‘NINTENDO’, ‘PC’]
2	[‘PC’, ‘NINTENDO’, ‘IOS’, ‘NINTENDO’]
3	[‘NINTENDO’, ‘PC’]
4	[‘IOS’, ‘PC’, ‘PLAYSTATION’, ‘PLAYSTATION’]
5	[‘XBOX’, ‘PLAYSTATION’, ‘IOS’, ‘NINTENDO’, ‘XBOX’]

Rows: 5Execution time: 11.40ms

Lambda function example: ARRAY_FIRST

ARRAY_FIRST returns the first element for which the Lambda predicate returns true, or NULL if no match is found.

SELECT playerid, nickname,
    ARRAY_FIRST((p) -> p = 'iOS', platforms) AS first_ios
FROM players
ORDER BY playerid LIMIT 5

playerid int null	nickname text null	first_ios text null
1	steven70	NULL
2	burchdenise	iOS
3	stephanie86	NULL
4	sabrina21	iOS
5	kennethpark	iOS

Rows: 5Execution time: 12.11ms

Optionally, you can omit the parentheses around the variables:

SELECT playerid, nickname,
    ARRAY_FIRST(p -> p = 'iOS', platforms) AS first_ios
FROM players
ORDER BY playerid LIMIT 5

Lambda function example: multiple arrays

ARRAY_SORT sorts one array by another. One array represents the values and the other represents the sort order. The example below sorts the first array by the positions defined in the second array.

SELECT ARRAY_SORT((x, y) -> y, ['A', 'B', 'C'], [3, 2, 1]) AS res;

res array(text)
[‘C’, ‘B’, ‘A’]

Rows: 1Execution time: 5.27ms

Optionally, you can omit the parentheses around the variables:

SELECT ARRAY_SORT(x, y -> y, ['A', 'B', 'C'], [3, 2, 1]) AS res;

UNNEST

You might want to transform a nested array structure to a standard tabular format. UNNEST serves this purpose. UNNEST is a table-valued function (TVF) that transforms an input row containing an array into a set of rows. UNNEST unfolds the elements of the array and duplicates all other columns found in the SELECT clause for each array element. If the input array is empty, the corresponding row is eliminated. You can use a single UNNEST command to unnest several arrays if the arrays are the same length. Multiple UNNEST statements in a single FROM clause result in a Cartesian product. Each element in the first array has a record in the result set corresponding to each element in the second array.

Example: single UNNEST with single ARRAY-typed column

The following example unnests the platforms column from the players table.

SELECT playerid, nickname, platform
FROM players, UNNEST(platforms) AS r(platform)
ORDER BY playerid, platform LIMIT 8

playerid int null	nickname text null	platform text null
1	steven70	Nintendo
1	steven70	PC
2	burchdenise	Nintendo
2	burchdenise	Nintendo
2	burchdenise	PC
2	burchdenise	iOS
3	stephanie86	Nintendo
3	stephanie86	PC

Rows: 8Execution time: 7.65ms

Example: single UNNEST using multiple ARRAY-typed columns

The following query specifies both the platforms column and ARRAY_ENUMERATE(platforms) to unnest, pairing each element with its position.

SELECT playerid, platform, idx
FROM players, UNNEST(platforms, ARRAY_ENUMERATE(platforms)) AS r(platform, idx)
ORDER BY playerid, idx LIMIT 8

playerid int null	platform text null	idx int
1	Nintendo	1
1	PC	2
2	PC	1
2	Nintendo	2
2	iOS	3
2	Nintendo	4
3	Nintendo	1
3	PC	2

Rows: 8Execution time: 6.62ms

Example: multiple UNNEST clauses resulting in a Cartesian product

The following query, while valid, creates a Cartesian product.

SELECT playerid, p1, p2
FROM players,
    UNNEST(platforms) AS r1(p1),
    UNNEST(platforms) AS r2(p2)
WHERE playerid = 1
ORDER BY p1, p2

playerid int	p1 text null	p2 text null
1	Nintendo	Nintendo
1	Nintendo	PC
1	PC	Nintendo
1	PC	PC

Rows: 4Execution time: 12.33ms

Example: error on UNNEST of multiple arrays with different lengths

The following query is invalid and will result in an error as the platforms and the literal two-element arrays have different lengths.

SELECT playerid, platform, x
FROM players, UNNEST(platforms, [1, 2]) AS r(platform, x);

ARRAY input and output syntax

ARRAY values can be converted from and to TEXT. This happens, for example, when an explicit CAST is added to a query, or when ARRAY values are (de-)serialized in a COPY statement.

Converting ARRAY to TEXT

Broadly, the TEXT representation of an ARRAY value starts with an opening curly brace ({). This is followed by the TEXT representations of the individual array elements separated by commas (,). It ends with a closing curly brace (}). NULL array elements are represented by the literal string NULL. For example, the query

SELECT
    CAST([1,2,3,4,NULL] AS TEXT)

array text
{1,2,3,4,NULL}

Rows: 1Execution time: 5.55ms

When converting ARRAY values containing TEXT elements to TEXT, some additional rules apply. Specifically, array elements are enclosed by double quotes (") in the following cases:

The array element is an empty string.
The array element contains curly or square braces ({,[,],}), commas (,), double quotes ("), backslashes (\), or white space.
The array element matches the word NULL (case-insensitively).

Additionally, double quotes and backslashes embedded in array elements will be backslash-escaped. For example, the query

SELECT
    CAST(['1','2','3','4',NULL,'','{impostor,array}','["impostor","array","back\slash"]',' padded and spaced ', 'only spaced', 'null'] AS TEXT)

returns the TEXT value

'{1,2,3,4,NULL,"","{impostor,array}","[\"impostor\",\"array\",\"back\\slash\"]"," padded and spaced ","only spaced","null"}'

Converting TEXT to ARRAY

When converting the TEXT representation of an array back to ARRAY, the same quoting and escaping rules as above apply. Unquoted whitespace surrounding array elements is trimmed, but whitespace contained within array elements is preserved. The array elements themselves are converted according to the conversion rules for the requested array element type. For example, the query

SELECT
    CAST('{1, 2, 3, 4, null, "", "{impostor,array}", "[\"impostor\",\"array\",\"back\\slash\"]", " padded and spaced ", "null",   unquoted padded and spaced   }' AS ARRAY(TEXT))

returns the ARRAY(TEXT) value

[1,2,3,4,NULL,'','{impostor,array}','["impostor","array","back\slash"]',' padded and spaced ','null','unquoted padded and spaced']

. It is also possible to enclose arrays with square braces ([ and ]) instead of curly braces ({ and }) when converting from TEXT to ARRAY. For example, the query

SELECT
    CAST('[1, 2, 3, 4, NULL]' AS ARRAY(INTEGER))

?column? array(int null)
[1, 2, 3, 4, NULL]

Rows: 1Execution time: 7.62ms

Nested ARRAYs

Finally, the same procedure applies when converting nested ARRAY values from and to TEXT. For example, the query

SELECT
    CAST([NULL,[],[NULL],[1,2],[3,4]] AS TEXT)

array text
{NULL,{},{NULL},{1,2},{3,4}}

Rows: 1Execution time: 6.04ms

The following query converts back from TEXT to ARRAY(ARRAY(INTEGER)):

SELECT
    CAST('{NULL,{},{1,2},{3,4}}' AS ARRAY(ARRAY(INTEGER)))

?column? array(array(int null) null)
[NULL, [], [1, 2], [3, 4]]

Rows: 1Execution time: 5.63ms

Overview

Performance and Observability

Security

Self-Managed

Managed service

Guides

SQL reference

Release notes

API reference

Legal

Declaring ARRAY data types in table definitions

Basis for examples

Comparing values against arrays

Simple array functions

LENGTH example

ARRAY_CONCAT example

ARRAY_FLATTEN example

Manipulating arrays with Lambda functions

Lambda function general syntax

Lambda function example: single array

Lambda function example: ARRAY_FIRST

Lambda function example: multiple arrays

UNNEST

Example: single UNNEST with single ARRAY-typed column

Example: single UNNEST using multiple ARRAY-typed columns

Example: multiple UNNEST clauses resulting in a Cartesian product

Example: error on UNNEST of multiple arrays with different lengths

ARRAY input and output syntax

Converting ARRAY to TEXT

Converting TEXT to ARRAY

Nested ARRAYs

​Declaring ARRAY data types in table definitions

​Basis for examples

​Comparing values against arrays

​Simple array functions

​LENGTH example

​ARRAY_CONCAT example

​ARRAY_FLATTEN example

​Manipulating arrays with Lambda functions

​Lambda function general syntax

​Lambda function example: single array

​Lambda function example: ARRAY_FIRST

​Lambda function example: multiple arrays

​UNNEST

​Example: single UNNEST with single ARRAY-typed column

​Example: single UNNEST using multiple ARRAY-typed columns

​Example: multiple UNNEST clauses resulting in a Cartesian product

​Example: error on UNNEST of multiple arrays with different lengths

​ARRAY input and output syntax

​Converting ARRAY to TEXT

​Converting TEXT to ARRAY

​Nested ARRAYs

Declaring ARRAY data types in table definitions

Basis for examples

Comparing values against arrays

Simple array functions

LENGTH example

ARRAY_CONCAT example

ARRAY_FLATTEN example

Manipulating arrays with Lambda functions

Lambda function general syntax

Lambda function example: single array

Lambda function example: ARRAY_FIRST

Lambda function example: multiple arrays

UNNEST

Example: single UNNEST with single ARRAY-typed column

Example: single UNNEST using multiple ARRAY-typed columns

Example: multiple UNNEST clauses resulting in a Cartesian product

Example: error on UNNEST of multiple arrays with different lengths

ARRAY input and output syntax

Converting ARRAY to TEXT

Converting TEXT to ARRAY

Nested ARRAYs