Nested type
AWS Clean Rooms supports queries involving data with nested data types, specifically the AWS Glue struct, array, and map column types. Only the custom analysis rule supports nested data types.
Notably, nested data types don't conform to the rigid, tabular structure of the relational data model of SQL databases.
Nested data types contains tags that reference distinct entities within the data. They can contain complex values such as arrays, nested structures, and other complex structures that are associated with serialization formats, such as JSON. Nested data types support up to 1 MB of data for an individual nested data type field or object.
Examples of nested data types
For the struct<given:varchar, family:varchar>
type, there are two
attribute names: given
, and family
, each corresponding to a
varchar
value.
For the array<varchar>
type, the array is specified as a list of
varchar
.
The array<struct<shipdate:timestamp, price:double>>
type refers to
a list of elements with struct<shipdate:timestamp, price:double>
type.
The map
data type behaves like an array
of
structs
, where the attribute name for each element in the array is
denoted by key
and it maps to a value
.
For example, the map<varchar(20), varchar(20)>
type is treated as
array<struct<key:varchar(20), value:varchar(20)>>
, where
key
and value
refer to the attributes of the map in the
underlying data.
For information about how AWS Clean Rooms enables navigation into arrays and structures, see Navigation.
For information about how AWS Clean Rooms enables iteration over arrays by navigating the array using the FROM clause of a query, see Unnesting queries.