Firebolt’s
STRUCT
data type is currently in public preview. We are gathering feedback and further refining this feature.STRUCT
data type.
Overview
The struct data type is a composite type that allows you to group multiple attributes of varying data types into a single logical unit. Common in modern databases, structs enable flexible and hierarchical data modeling by representing related data as a single object with named attributes. This is especially useful for semi-structured or nested data, allowing for clearer organization, better readability, and simplified queries when working with complex data relationships.Defining struct types
A struct type is defined using theSTRUCT(...)
syntax:
Nullability constraint
A nullability constraint can be specified to a struct column but not any struct attribute. This means struct attributes are always nullable, even if the enclosing struct isNOT NULL
.
For example, defining the kv
column to be NOT NULL
is OK. Defining the s_key
or value
attribute to be NOT NULL
is not supported.
kv
cannot store NULL
values for the whole struct. But a struct value where both s_key
and value
attributes are NULL
is valid.
Although specifying the NOT NULL
constraint to a struct attribute yields an error, declaring a struct attribute as NULL
explicitly is accepted. It is redundant but may be used to improve readability.
Partition on struct or struct attributes
Partitioning a table on struct columns or struct attributes is not yet supported, but will be released in the near future.Creating struct values
You can create struct values with either unnamed fields or named fields.Struct values with unnamed fields
Use theSTRUCT(...)
syntax to create struct values with unnamed fields. For example:
Struct values with named fields
To create struct values with named fields, use the following syntax:Ingesting struct values
You can use theINSERT
statement to insert struct values into a table. For example:
COPY FROM
statement. For example:
- When
SUPPORT_STRUCTS=TRUE
: Nested structures in the source data will be inferred asSTRUCT
data types. - When
SUPPORT_STRUCTS=FALSE
: Nested structures in the source data will be inferred as individual shredded columns.
The
SUPPORT_STRUCTS
parameter only affects type inference during schema discovery. With predefined table schemas, the system maps incoming data to your schema regardless of this setting. For more details, see the COPY FROM
documentation.COPY FROM
statement supports the STRUCT
data type for Parquet files. Support of other file formats will be added in the near future.
External tables do not support the STRUCT
data type at the moment. We will extend the coverage in the following releases.
Querying structs
In Firebolt, each attribute of a struct is stored as an individual column. When processing struct values, the system never packs attributes of a struct into a binary format. Instead, it always works on a list of attribute columns directly. The query optimizer applies column pruning techniques on struct attributes, too. This means Firebolt only accesses struct attributes that are strictly needed for answering a query.Accessing attributes
A struct attribute can be accessed by using the dot syntax:value
, without adding the struct name or any extra formatting to the column name.
In addition, the PostgreSQL syntax for accessing a field of a composite type is also supported:
t.a
can be resolved to either a column a
of table t
or an attribute a
of a struct t
, the Firebolt syntax t.a
prefers the table column over the struct attribute. On the other hand, the PostgreSQL syntax (t).a
prefers the struct attribute over the table column.
When there is no such ambiguity, both syntaxes resolve to the same object and they can be used interchangeably.
Type conversion
At the moment, aSTRUCT
value cannot be converted to or from any other non-struct data type.
Struct types consider both field names and field positions. To determine a common type of two struct types or whether one struct type can be converted to another struct type, the two struct types must have exactly the same structure.
Common type of two struct types
- A common type of struct types is derived as a struct type whose field types are common types of each input field type at the same position. If at any position the struct field types do not have a common type, the struct types do not have a common type.
- The common type of unnamed struct types is still unnamed.
- The common type of an unnamed struct and a named struct is named, following the struct field names of the named struct type.
- Two named struct types only have a common type if their field names are equal at each position.
Assigning struct values to another struct type
- A struct value can be assigned to another struct type if each source field type can be assigned to the target field type at the same position.
- Unnamed struct types can be assigned to both unnamed and named struct types.
- Named struct types can only be assigned to other named struct types if each struct field name matches the target struct field name at the same position, unless the conversion is forced by an explicit cast to override field names.
Grouping and ordering
Struct columns can be used inGROUP BY
and ORDER BY
clauses of a SELECT
statement:
ORDER BY kv
means ordering by "s_key"
attribute first, and then "value"
.
Understanding EXPLAIN
output with structs
Because struct values are processed through columnar decomposition, it’s important to consider how struct attributes appear as separate columns, how nullability is tracked with marker columns, and how nodes like Projection
and Sort
operate on decomposed fields. These details are especially relevant when interpreting the EXPLAIN
output for queries on STRUCT
columns.
Let us take a look at the EXPLAIN
output of the last example query above:
StoredTable
node [2] shows “5/5 column(s)”. Recall that struct values are decomposed into individual attribute columns. So table t
is actually decomposed into the following schema:
kv.s_key
and kv.value
, there is a third column kv.$not_null$
that denotes whether the whole struct value is NULL
. This column exists only if the struct is defined as nullable.
The Sort
node [1] directly orders tuples based on the decomposed struct attributes, including the $not_null$
marker column.
The top Projection
node [0]
constructs struct values for the kv
column, as required by the query. However, instead of packing these values into actual struct objects, Firebolt serializes the result directly using the list of attribute columns.
Limitations
At the moment, built-in functions and operators generally do not accept struct values, including comparison operators. Use struct attributes in queries wherever possible. More functions and operators supporting struct values will be added in the near future.Type composition
Firebolt supports bothARRAY
and STRUCT
composite types. They compose freely, so you can model data as arrays of structs, structs of arrays, or more complicated shapes.
Nested struct
A struct attribute can have any type that is supported in Firebolt, including theSTRUCT
type itself. This means nested STRUCT
types are allowed. For example:
Array of struct
TheSTRUCT
type composes with the ARRAY
type as well. It is possible to define an array of struct:
STRUCT
or an array of STRUCT
. As a result, for an array of struct column, only the top-level array can be NOT NULL
. All nested element types are nullable by default.
At the moment, array functions and lambda functions generally do not support array of struct. The support will be extended in the near future.
Firebolt supports using array subscription ([]
syntax) to access an array element on an array of STRUCT
values.
When accessing an attribute of a struct in an array, the combined syntax
arr[1].a
does not work at the moment. Use the PostgreSQL syntax instead: (arr[1]).a
. The issue will be fixed in the near future.Unnesting arrays of structs
Unnesting an array of structs is just the same as unnesting an array of any other type:UNNEST
.
Aggregating structs into an array of struct
Some aggregate functions support structs. In particular,ARRAY_AGG
can be useful when working on array of struct, together with UNNEST
.
s_key > 10
from an array of struct column and preserves the array structure.
Using UNNEST
and ARRAY_AGG
together provides a fundamental way of working with arrays of struct even if there is no array or lambda function available for the operation in need.
Transforming arrays of structs
The example query above can be simplified by using theARRAY_TRANSFORM
function.
ARRAY_TRANSFORM
can be used to extract an attribute from an array of structs while keeping the array structure. Once having an array of primitive types, all other array functions can be used to process the array further. In this example, ARRAY_FILTER
is used to select keys that are bigger than 10.