FLATTEN

    For example:

    • The table resulting from on the left column:

    YDB tables don’t support container types, so the FLATTEN BY function can only be applied to table-type variables created within a YQL query.

    Example

    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 .

    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 typeResult typeComments
    List<X>XList cell type
    Dict<X,Y>Tuple<X,Y>Tuple of two elements containing key-value pairs
    Optional<X>XThe 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 .
    • 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.
    • 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.

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

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

    • 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

    Examples

    FLATTEN - 图2

    FLATTEN - 图4

    Analogs of FLATTEN BY in other DBMS

    • PostgreSQL: unnest
    • Hive: LATERAL VIEW
    • MongoDB: unwind
    • Google BigQuery: FLATTEN

    FLATTEN COLUMNS

    Converts a table where all columns must be structures to a table with columns corresponding to each element of each structure from the source columns.

    The names of the source column structures are not used and not returned in the result. Be sure that the structure element names aren’t repeated in the source columns.

    Example