snowflake.snowpark.functions.flatten¶

snowflake.snowpark.functions.flatten(col: Union[Column, str], path: str = '', outer: bool = False, recursive: bool = False, mode: Literal['object', 'array', 'both'] = 'both') → TableFunctionCall[source] (https://github.com/snowflakedb/snowpark-python/blob/v1.16.0/src/snowflake/snowpark/functions.py#L1319-L1398)¶

FLATTEN explodes compound values into multiple rows. This table function takes a VARIANT, OBJECT, or ARRAY column and produces a lateral view.

Parameters:

• col – Column object or string name of the desired column.

• path – The path to the element within VARIANT data structure which needs to be flattened. Defaults to “”.

• outer – When False, any input rows that cannot be expanded are completely omitted from the output. When True, exactly one row s generated for zero-row expansions. Defaults to False.

• recursive – When False, only the reference by path is expanded. When True, the expansion is performed for all sub-elements recursively. Defaults to False.

• mode – Specifies whether only objects, arrays, or both should be flattened. Defaults to “both”.

Examples::

>>> df = session.create_dataframe([[1, [1, 2, 3], {"Ashi Garami": ["X", "Leg Entanglement"]}, "Kimura"],
...                                [2, [11, 22], {"Sankaku": ["Triangle"]}, "Coffee"],
...                                [3, [], {}, "empty"]],
...                                schema=["idx", "lists", "maps", "strs"])
>>> df.select(df.idx, flatten(df.lists, outer=True)).select("idx", "value").sort("idx").show()
-------------------
|"IDX"  |"VALUE"  |
-------------------
|1      |1        |
|1      |2        |
|1      |3        |
|2      |11       |
|2      |22       |
|3      |NULL     |
-------------------

Copy

>>> df.select(df.strs, flatten(df.maps, recursive=True)).select("strs", "key", "value").where("key is not NULL").sort("strs").show()
-----------------------------------------------
|"STRS"  |"KEY"        |"VALUE"               |
-----------------------------------------------
|Coffee  |Sankaku      |[                     |
|        |             |  "Triangle"          |
|        |             |]                     |
|Kimura  |Ashi Garami  |[                     |
|        |             |  "X",                |
|        |             |  "Leg Entanglement"  |
|        |             |]                     |
-----------------------------------------------

Copy

>>> df.select(df.strs, flatten(df.maps, recursive=True)).select("strs", "key", "value").where("key is NULL").sort("strs", "value").show()
---------------------------------------
|"STRS"  |"KEY"  |"VALUE"             |
---------------------------------------
|Coffee  |NULL   |"Triangle"          |
|Kimura  |NULL   |"Leg Entanglement"  |
|Kimura  |NULL   |"X"                 |
---------------------------------------

Copy

See also

• explode()

• Flatten