Array, collection, and struct functions

Preview Spark array, collection, struct, and map functions in SparkSQL and Databricks compatibility modes. 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`	Supported
`ARRAY_COMPACT`	Supported
`ARRAY_CONTAINS`	Supported
`ARRAY_DISTINCT`	Supported
`ARRAY_MAX`	Supported
`ARRAY_MIN`	Supported
`ARRAY_SIZE`	Supported
`ARRAYS_OVERLAP`	Supported
`FLATTEN`	Supported
`GET`	Supported
`ARRAY_APPEND`	With limitations	`array_append(CAST(null as Array<Int>), 2)` is not supported.
`ARRAY_INTERSECT`	With limitations	`array_intersect(array(1, 2, 3), array(1, 3, 5))` returns `{3,1}`, where Spark returns `{1,3}`.
`ARRAY_JOIN`	With limitations	`array_join(array('hello', null ,'world'), ' ', ',')` is not supported.
`ARRAY_POSITION`	With limitations	`array_position(array(312, 773, 708, 708), 414)` returns `NULL`, where Spark returns `0`.
`ARRAY_PREPEND`	With limitations	`array_prepend(CAST(null as Array<Int>), 2)` is not supported.
`SLICE`	With limitations	`slice(array(1, 2, 3, 4), -2, 2)` raises an error, where Spark returns `{3,4}`.
`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`	With limitations	Not supported for `MAP` arguments.
`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`	With limitations	Not supported for `MAP` arguments.
`REVERSE`	With limitations	`reverse(array(2, 1, 4, 3))` is not supported.
`SIZE`	With limitations	Not supported for `MAP` arguments.
`TRY_ELEMENT_AT`	Not supported

Higher-order functions

Function	Support	Notes
`FORALL`	Supported
`ARRAY_SORT`	With limitations	The comparator-lambda form is not supported.
`FILTER`	With limitations	`filter(array(0, 2, 3), (x, i) -> x > i)` is not supported.
`TRANSFORM`	With limitations	`transform(array(1, 2, 3), (x, i) -> x + i)` is not supported.
`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`	With limitations	`named_struct("a", 1, "b", 2, "c", 3)` returns `{a:1,b:2,c:3}`, where Spark returns `(1,2,3)`.
`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

Overview

Performance and Observability

Security

Self-Managed

Managed service

Guides

SQL reference

Release notes

API reference

Legal

Array, collection, and struct functions

Array functions

Collection functions

Higher-order functions

Struct functions

Map functions

​Array functions

​Collection functions

​Higher-order functions

​Struct functions

​Map functions

Array functions

Collection functions

Higher-order functions

Struct functions

Map functions