Tables can have columns of complex types, including nested complex types. You can use the ROW (struct), ARRAY, and SET types in native and external tables. Sets are limited to one-dimensional collections of scalar types. A limited MAP type is available for external tables, but you can use ARRAY and ROW to express a map instead. Selected parsers support loading data with complex types.
You can define a column for heterogeneous combinations of the ARRAY and ROW types: a struct containing array fields or an array of structs. These types can be nested up to the maximum nesting depth of 100.
Restrictions for Native Tables
Complex types used in native tables have some restrictions, in addition to the restrictions for individual types listed on their reference pages:
- A native table must have at least one column that is a primitive type or a native array (one-dimensional array of a primitive type).
- Complex type columns cannot be used in ORDER BY or PARTITION BY clauses nor as FILLER columns.
- Complex type columns cannot have constraints.
- Expressions returning complex types cannot be used as projection columns, and projections cannot be segmented or ordered by columns of complex types.
- Tables with columns of complex types cannot use DEFAULT and SET USING.
- See CREATE TABLE and ALTER TABLE for additional restrictions.
Alternative to Strong Typing
Though you can fully specify a column representing any combination of ROW and ARRAY types, there might be cases where you prefer a more flexible approach. If the data contains a struct with hundreds of fields, only a few of which you need, you might prefer to extract just those few at query time instead of defining all of the fields. Similarly, if the data structure is likely to change, you might prefer to defer fully specifying the complex types. You can use flexible columns as an alternative to fully specifying the structure of a complex column. This is the same approach used for flex tables, where all data is initially loaded into a single binary column and materialized from there as needed. See Flexible Complex Types for more information about using this approach.