> ## Documentation Index
> Fetch the complete documentation index at: https://docs.e6data.com/llms.txt
> Use this file to discover all available pages before exploring further.

# FLATTEN

> Flattens one array nesting level in Databricks scalar context, or expands a collection as a table in Snowflake-style context.

<div className="e6-meta"><span className="e6-eyebrow e6-eyebrow--quiet">Scalar · Table</span></div>

Scalar NULL outer array or NULL inner array returns NULL; NULL elements within valid inner arrays remain NULL.

## Syntax

### Scalar

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
flatten(array)
```

* Requires the Databricks SQL context with native execution.

### Table

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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

Type notation used below:

* 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

| Name | Type | Required | Description |
| - | - | - | - |
| array | ARRAY\<ARRAY\<T>> | Yes | Nested array to concatenate one level. Nested elements/fields must satisfy the same element-type restrictions. |

### Table

| Name | Type | Required | Description |
| - | - | - | - |
| INPUT | ARRAY or VARIANT | Yes | Collection to expand. ARRAY elements can be BOOLEAN, TINYINT, SMALLINT, INTEGER, BIGINT, FLOAT, DOUBLE, DECIMAL(p, s), STRING, BINARY, DATE, TIME, TIMESTAMP, ARRAY, MAP or STRUCT. Expansion preserves each element type. VARIANT arrays/objects produce VARIANT values; SQL MAP is not a direct input to this table form. |
| OUTER | BOOLEAN literal | No | Preserve empty/NULL input rows. Must be a constant argument. Default: FALSE. |

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

| Property | Value |
| - | - |
| Deterministic | Yes |
| Supports window usage | No |
| Exact result | Yes |

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

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT flatten(ARRAY(ARRAY(1, 2), ARRAY(3, 4))) AS result;
```

| result |
| - |
| \[1,2,3,4] |

### Typical behavior (table)

* Snowflake-style table FLATTEN context.

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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;
```

| pos | value |
| - | - |
| 0 | 10 |
| 1 | 20 |

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


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