> ## Documentation Index > Fetch the complete documentation index at: https://docs.firebolt.io/llms.txt > Use this file to discover all available pages before exploring further. > Learn techniques to manipulate and transform arrays in Firebolt. # Arrays This section covers querying and manipulating arrays in Firebolt. ## Declaring ARRAY data types in table definitions Array types are declared using `ARRAY()` where `` 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`. ```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}} 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. ```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}} 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)](/reference-sql/lexical-structure/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](/reference-sql/functions-reference/array/array-length), [ARRAY\_CONCAT](/reference-sql/functions-reference/array/array-concat), and [ARRAY\_FLATTEN](/reference-sql/functions-reference/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](/reference-sql/functions-reference/lambda). ``` ([, ][, ...] -> , [, ][, ...]) ``` | Parameter | Description | | :------------------------------------------ | :------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | | `` | Any array function that accepts a Lambda expression as an argument. For a list, see [Lambda functions](/reference-sql/functions-reference/lambda). | | `[, ][, ...]` | 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 (`[, ][, ...]`). At runtime, each variable contains an element of the corresponding array. The specified `` is performed for each variable. | | `` | The operation that is performed for each element of the array. This is typically a function or Boolean expression. | | `[, ][, ...]` | A comma-separated list of expressions, each of which evaluates to an `ARRAY` data type. | Optionally, you can enclose the variables in parentheses: ``` (([, ][, ...]) -> , [, ][, ...]) ``` ### Lambda function example: single array Consider the following [TRANSFORM](/reference-sql/functions-reference/lambda/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](/reference-sql/functions-reference/lambda/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: ```sql theme={"theme":{"light":"css-variables","dark":"css-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](/reference-sql/functions-reference/array/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: ```sql theme={"theme":{"light":"css-variables","dark":"css-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](/reference-sql/commands/queries/select#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. ```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}} 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 ```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}} 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 ```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}} 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