structs and arrays are how developers store nested data within bigquery's columnar structure.

what is a struct?

a struct is a collection of fields with a specified type, which is required, and a field name, which is optional. notably, struct types, unlike arrays, can contain a mixture of data types.

to better understand the struct type, take another look at the example from the previous chapter, now with a few changes.

"locations": [
   {"store_no": 4, "employee_count": 15, "store_name": "new york 5th ave"}, 
   {"store_no": 5, "employee_count": 30, "store_name": "new york lower manh"}
]

while previously "locations" was a dict (expressed in json) of the same type, now it contains two structs which have a type of <integer, integer, string>.

despite supporting a struct type, bigquery does not have an explicit label struct available during the table creation stage.
instead, a struct is indicated as a record with a nullable mode.

note − think of a struct as more of a container than a dedicated data type.

when defined in a schema, the elements inside the struct will be represented and selected with a "." here, the schema would be −

{"locations", "record", "nullable"},
{"locations.store_no", "integer", "nullable},
{"locations.employee_count", "integer", "nullable"},
{"locations.store_name", "string", "nullable"}

dot notation

to select a struct element, it is necessary to use the dot notation in the from clause −

when the query gets executed, you may get an output like this −

Save or share this article