FLATTEN

​

Syntax

​

Scalar

flatten(array)

• Requires the Databricks SQL context with native execution.

​

Table

TABLE(FLATTEN(INPUT => collection [, OUTER => outer]))

• Snowflake-style table FLATTEN context.
• Use LATERAL FLATTEN with input from the preceding table in FROM. A standalone constant TABLE(FLATTEN(…)) call is not supported.

​

Arguments

• T is the array element type: BOOLEAN, TINYINT, SMALLINT, INTEGER, BIGINT, FLOAT, DOUBLE, DECIMAL(p, s), STRING, BINARY, DATE, TIME, TIMESTAMP, ARRAY, MAP or STRUCT.

​

Scalar

​

Table

​

Returns

• Type: ARRAY<T> (scalar) or TABLE (table form)
• NULL behavior: Scalar NULL outer array or NULL inner array returns NULL; NULL elements within valid inner arrays remain NULL.
• Shape: Scalar concatenated ARRAY<T>; table form has SEQ, KEY, PATH, INDEX, VALUE and THIS.
• Ordering: Scalar array order is preserved. Table row order requires ORDER BY.

​

Scalar

• Type: ARRAY<T>
• NULL behavior: Scalar NULL outer array or NULL inner array returns NULL; NULL elements within valid inner arrays remain NULL.
• Empty input: Empty outer/inner arrays concatenate to an empty array.
• Shape: Scalar concatenated ARRAY<T>; table form has SEQ, KEY, PATH, INDEX, VALUE and THIS.
• Ordering: Scalar array order is preserved. Table row order requires ORDER BY.

​

Table

• Type: TABLE<SEQ:BIGINT,KEY:STRING,PATH:STRING,INDEX:BIGINT,VALUE:element type,THIS:input array type>
• NULL behavior: With OUTER=false, empty/NULL collection inputs produce no rows; OUTER=true preserves them as a NULL-valued row.
• Empty input: No rows unless OUTER=true, which preserves one NULL-valued row.
• Shape: Six declared columns: SEQ, KEY, PATH, INDEX, VALUE, THIS.
• Ordering: SQL row order requires ORDER BY.

​

Properties

​

Usage notes

• Scalar FLATTEN removes exactly one nesting level and preserves element order.
• The table form accepts INPUT and optional OUTER. PATH, RECURSIVE, and MODE are not supported.
• Table SEQ and PATH are currently NULL placeholders; typed-array KEY is also NULL. ARRAY table indices are zero-based. VARIANT objects provide KEY and VARIANT arrays provide INDEX.
• INTEGER and INT mean the same type; STRING means VARCHAR text. NULL can be used with a supported type. Cast NULL explicitly when its type would otherwise be unclear.
• Deterministic: The same inputs and settings produce the same result.

​

Examples

​

Typical behavior (scalar)

• This scalar form requires the Databricks SQL context with native execution.

SELECT flatten(ARRAY(ARRAY(1, 2), ARRAY(3, 4))) AS result;

​

Typical behavior (table)

• Snowflake-style table FLATTEN context.

SELECT f."INDEX" AS pos, f."VALUE" AS "value" FROM (VALUES (ARRAY[10, 20])) AS t(arr), LATERAL FLATTEN(INPUT => t.arr) AS f ORDER BY pos;

​

Limitations

• The SQL dialect selects the form: Databricks FLATTEN returns an array, while the Snowflake-style form returns rows.
• VARIANT table expansion produces one level of elements. Primitive values and JSON null do not produce ordinary element rows.
• SQL MAP is not a direct table-form input; object expansion uses a VARIANT object.