FLATTEN

FLATTEN BY

Converts rows in the source table using vertical unpacking of containers of variable length (lists or dictionaries).

For example:

Source table:

[a, b, c] 1

[d] 2

[] 3
The table resulting from FLATTEN BY on the left column:

a 1

b 1

c 1

d 2

[a, b, c]	1
[d]	2
[]	3

a	1
b	1
c	1
d	2

Example

$sample = AsList(
    AsStruct(AsList('a','b','c') AS value, CAST(1 AS Uint32) AS id),
    AsStruct(AsList('d') AS value, CAST(2 AS Uint32) AS id),
    AsStruct(AsList() AS value, CAST(3 AS Uint32) AS id)
);

SELECT value, id FROM as_table($sample) FLATTEN BY (value);

This conversion can be convenient in the following cases:

When it is necessary to output statistics by cells from a container column (for example, via GROUP BY).
When the cells in a container column store IDs from another table that you want to join with JOIN.

Syntax

FLATTEN BY is specified after FROM, but before GROUP BY, if GROUP BY is present in the query.
The type of the result column depends on the type of the source column:

Container type	Result type	Comments
`List<X>`	`X`	List cell type
`Dict<X,Y>`	`Tuple<X,Y>`	Tuple of two elements containing key-value pairs
`Optional<X>`	`X`	The result is almost equivalent to the clause `WHERE foo IS NOT NULL`, but the `foo` column type is changed to `X`

By default, the result column replaces the source column. Use FLATTEN BY foo AS bar to keep the source container. As a result, the source container is still available as foo and the output container is available as bar.
To build a Cartesian product of multiple container columns, use the clause FLATTEN BY (a, b, c). Parentheses are mandatory to avoid grammar conflicts.
Inside FLATTEN BY, you can only use column names from the input table. To apply FLATTEN BY to the calculation result, use a subquery.
In FLATTEN BY you can use both columns and arbitrary named expressions (unlike columns, AS is required in this case). To avoid grammatical ambiguities of the expression after FLATTEN BY, make sure to use parentheses with the following: ... FLATTEN BY (ListSkip(col, 1) AS col) ...
If the source column had nested containers, for example, List<DictX,Y>, FLATTEN BY unpacks only the outer level. To completely unpack the nested containers, use a subquery.

Note

FLATTEN BY interprets optional data types as lists of length 0 or 1. The table rows with NULL are skipped, and the column type changes to a similar non-optional type.

FLATTEN BY makes only one conversion at a time, so use FLATTEN LIST BY or FLATTEN OPTIONAL BY on optional containers, for example, Optional<List<String>>.

Applying FLATTEN BY to YSON

To apply FLATTEN BY to YSON, you first need to transform it into one of the container types discussed above. For instance, you can do this using the UDF module of the same name from the list of presets.

Specifying the container type

To specify the type of container to convert to, you can use:

FLATTEN LIST BY

For Optional<List<T>>, FLATTEN LIST BY will unpack the list, treating NULL as an empty list.
FLATTEN DICT BY

For Optional<Dict<T>>, FLATTEN DICT BY will unpack the dictionary, interpreting NULL as an empty dictionary.
FLATTEN OPTIONAL BY

To filter the NULL values without serialization, specify the operation by using FLATTEN OPTIONAL BY.

Examples

SELECT
  t.item.0 AS key,
  t.item.1 AS value,
  t.dict_column AS original_dict,
  t.other_column AS other
FROM my_table AS t
FLATTEN DICT BY dict_column AS item;

SELECT * FROM (
    SELECT
        AsList(1, 2, 3) AS a,
        AsList("x", "y", "z") AS b
) FLATTEN LIST BY (a, b);

SELECT * FROM (
    SELECT
        "1;2;3" AS a,
        AsList("x", "y", "z") AS b
) FLATTEN LIST BY (String::SplitToList(a, ";") as a, b);

Analogues of FLATTEN BY in other DBMS

PostgreSQL: unnest
Hive: LATERAL VIEW
MongoDB: unwind
Google BigQuery: FLATTEN
ClickHouse: ARRAY JOIN / arrayJoin

FLATTEN COLUMNS

Transforms each column of type Struct into individual columns, one for each field within the struct. The names of the new columns are the names of the fields from the original struct columns. Columns that are not structs remain unchanged.

Only one level of the struct is flattened.
The original struct columns are not included in the result; their names are not used anywhere.
All column names in the resulting table (including names from struct fields in the original columns and names of non-struct columns) must be unique; name conflicts result in an error.

Example

SELECT x, y, z, not_struct
FROM (
  SELECT
    AsStruct(
        1 AS x,
        "foo" AS y),
    AsStruct(
        false AS z),
    1 as not_struct,
) FLATTEN COLUMNS;

EXPORT и IMPORT

INSERT