> ## 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.

# Array, collection, and struct functions

> Spark array, collection, and struct functions supported in SparkSQL and Databricks compatibility modes.

Preview

Spark [array](https://spark.apache.org/docs/latest/sql-ref-functions-builtin.html#array-functions), [collection](https://spark.apache.org/docs/latest/sql-ref-functions-builtin.html#collection-functions), [struct](https://spark.apache.org/docs/latest/sql-ref-functions-builtin.html#struct-functions), and [map](https://spark.apache.org/docs/latest/sql-ref-functions-builtin.html#map-functions) functions in [SparkSQL and Databricks compatibility modes](https://docs.firebolt.io/reference-sql/spark/overview.md). Both modes share these functions. Support is one of: Supported, With limitations, or Not supported. Supported means every example in the Spark reference returns what Spark returns; a note under With limitations quotes a call that differs.

## Array functions

| Function | Support | Notes |
| :- | :- | :- |
| [`ARRAY`](https://spark.apache.org/docs/4.1.0/api/sql/#array) | Supported | |
| [`ARRAY_COMPACT`](https://spark.apache.org/docs/4.1.0/api/sql/#array_compact) | Supported | |
| [`ARRAY_CONTAINS`](https://spark.apache.org/docs/4.1.0/api/sql/#array_contains) | Supported | |
| [`ARRAY_DISTINCT`](https://spark.apache.org/docs/4.1.0/api/sql/#array_distinct) | Supported | |
| [`ARRAY_MAX`](https://spark.apache.org/docs/4.1.0/api/sql/#array_max) | Supported | |
| [`ARRAY_MIN`](https://spark.apache.org/docs/4.1.0/api/sql/#array_min) | Supported | |
| [`ARRAY_SIZE`](https://spark.apache.org/docs/4.1.0/api/sql/#array_size) | Supported | |
| [`ARRAYS_OVERLAP`](https://spark.apache.org/docs/4.1.0/api/sql/#arrays_overlap) | Supported | |
| [`FLATTEN`](https://spark.apache.org/docs/4.1.0/api/sql/#flatten) | Supported | |
| [`GET`](https://spark.apache.org/docs/4.1.0/api/sql/#get) | Supported | |
| [`ARRAY_APPEND`](https://spark.apache.org/docs/4.1.0/api/sql/#array_append) | With limitations | `array_append(CAST(null as Array<Int>), 2)` is not supported. |
| [`ARRAY_INTERSECT`](https://spark.apache.org/docs/4.1.0/api/sql/#array_intersect) | With limitations | `array_intersect(array(1, 2, 3), array(1, 3, 5))` returns `{3,1}`, where Spark returns `{1,3}`. |
| [`ARRAY_JOIN`](https://spark.apache.org/docs/4.1.0/api/sql/#array_join) | With limitations | `array_join(array('hello', null ,'world'), ' ', ',')` is not supported. |
| [`ARRAY_POSITION`](https://spark.apache.org/docs/4.1.0/api/sql/#array_position) | With limitations | `array_position(array(312, 773, 708, 708), 414)` returns `NULL`, where Spark returns `0`. |
| [`ARRAY_PREPEND`](https://spark.apache.org/docs/4.1.0/api/sql/#array_prepend) | With limitations | `array_prepend(CAST(null as Array<Int>), 2)` is not supported. |
| [`SLICE`](https://spark.apache.org/docs/4.1.0/api/sql/#slice) | With limitations | `slice(array(1, 2, 3, 4), -2, 2)` raises an error, where Spark returns `{3,4}`. |
| [`SORT_ARRAY`](https://spark.apache.org/docs/4.1.0/api/sql/#sort_array) | With limitations | `sort_array(array('b', 'd', null, 'c', 'a'), true)` returns `{a,b,c,d,NULL}`, where Spark returns `{NULL,"a","b","c","d"}`. |
| `ARRAY_EXCEPT`, `ARRAY_INSERT`, `ARRAY_REMOVE`, `ARRAY_REPEAT`, `ARRAY_UNION`, `ARRAYS_ZIP`, `SEQUENCE`, `SHUFFLE` | Not supported | |

## Collection functions

| Function | Support | Notes |
| :- | :- | :- |
| [`CARDINALITY`](https://spark.apache.org/docs/4.1.0/api/sql/#cardinality) | With limitations | Not supported for `MAP` arguments. |
| [`CONCAT`](https://spark.apache.org/docs/4.1.0/api/sql/#concat) | With limitations | `concat(array(1, 2, 3), array(4, 5), array(6))` returns `'{1,2,3}{4,5}{6}'`, where Spark returns `{1,2,3,4,5,6}`. |
| [`ELEMENT_AT`](https://spark.apache.org/docs/4.1.0/api/sql/#element_at) | With limitations | Not supported for `MAP` arguments. |
| [`REVERSE`](https://spark.apache.org/docs/4.1.0/api/sql/#reverse) | With limitations | `reverse(array(2, 1, 4, 3))` is not supported. |
| [`SIZE`](https://spark.apache.org/docs/4.1.0/api/sql/#size) | With limitations | Not supported for `MAP` arguments. |
| `TRY_ELEMENT_AT` | Not supported | |

## Higher-order functions

| Function | Support | Notes |
| :- | :- | :- |
| [`FORALL`](https://spark.apache.org/docs/4.1.0/api/sql/#forall) | Supported | |
| [`ARRAY_SORT`](https://spark.apache.org/docs/4.1.0/api/sql/#array_sort) | With limitations | The comparator-lambda form is not supported. |
| [`FILTER`](https://spark.apache.org/docs/4.1.0/api/sql/#filter) | With limitations | `filter(array(0, 2, 3), (x, i) -> x > i)` is not supported. |
| [`TRANSFORM`](https://spark.apache.org/docs/4.1.0/api/sql/#transform) | With limitations | `transform(array(1, 2, 3), (x, i) -> x + i)` is not supported. |
| [`ZIP_WITH`](https://spark.apache.org/docs/4.1.0/api/sql/#zip_with) | With limitations | `zip_with(array(1, 2, 3), array('a', 'b', 'c'), (x, y) -> (y, x))` raises an error, where Spark returns `{("a",1),("b",2),("c",3)}`. |
| `AGGREGATE`, `EXISTS`, `MAP_FILTER`, `MAP_ZIP_WITH`, `REDUCE`, `TRANSFORM_KEYS`, `TRANSFORM_VALUES` | Not supported | |

## Struct functions

| Function | Support | Notes |
| :- | :- | :- |
| [`NAMED_STRUCT`](https://spark.apache.org/docs/4.1.0/api/sql/#named_struct) | With limitations | `named_struct("a", 1, "b", 2, "c", 3)` returns `{a:1,b:2,c:3}`, where Spark returns `(1,2,3)`. |
| [`STRUCT`](https://spark.apache.org/docs/4.1.0/api/sql/#struct) | With limitations | `struct(1, 2, 3)` returns `{col1:1,col2:2,col3:3}`, where Spark returns `(1,2,3)`. |

## Map functions

| Function | Support | Notes |
| :- | :- | :- |
| `MAP`, `MAP_CONCAT`, `MAP_CONTAINS_KEY`, `MAP_ENTRIES`, `MAP_FROM_ARRAYS`, `MAP_FROM_ENTRIES`, `MAP_KEYS`, `MAP_VALUES`, `STR_TO_MAP` | Not supported | |


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.