snowflake.snowpark.Session.flatten¶

Session.flatten(input: Union[snowflake.snowpark.column.Column, str], path: Optional[str] = None, outer: bool = False, recursive: bool = False, mode: str = 'BOTH') → DataFrame[source] (https://github.com/snowflakedb/snowpark-python/blob/v1.16.0/src/snowflake/snowpark/session.py#L2997-L3081)¶

Creates a new DataFrame by flattening compound values into multiple rows.

The new DataFrame will consist of the following columns:

SEQ

KEY

PATH

INDEX

VALUE

THIS

References: Snowflake SQL function FLATTEN.

Example:

df = session.flatten(parse_json(lit('{"a":[1,2]}')), "a", False, False, "BOTH")

Copy

Parameters:

input – The name of a column or a Column instance that will be unseated into rows. The column data must be of Snowflake data type VARIANT, OBJECT, or ARRAY.
path – The path to the element within a VARIANT data structure which needs to be flattened. The outermost element is to be flattened if path is empty or None.
outer – If False, any input rows that cannot be expanded, either because they cannot be accessed in the path or because they have zero fields or entries, are completely omitted from the output. Otherwise, exactly one row is generated for zero-row expansions (with NULL in the KEY, INDEX, and VALUE columns).
recursive – If False, only the element referenced by path is expanded. Otherwise, the expansion is performed for all sub-elements recursively.
mode – Specifies which types should be flattened “OBJECT”, “ARRAY”, or “BOTH”.

Returns:

A new DataFrame that has the flattened new columns and new rows from the compound data.

Example:

>>> from snowflake.snowpark.functions import lit, parse_json
>>> session.flatten(parse_json(lit('{"a":[1,2]}')), path="a", outer=False, recursive=False, mode="BOTH").show()
-------------------------------------------------------
|"SEQ"  |"KEY"  |"PATH"  |"INDEX"  |"VALUE"  |"THIS"  |
-------------------------------------------------------
|1      |NULL   |a[0]    |0        |1        |[       |
|       |       |        |         |         |  1,    |
|       |       |        |         |         |  2     |
|       |       |        |         |         |]       |
|1      |NULL   |a[1]    |1        |2        |[       |
|       |       |        |         |         |  1,    |
|       |       |        |         |         |  2     |
|       |       |        |         |         |]       |
-------------------------------------------------------

Copy