How to work with Arrays and Structs in Google BigQuery

If you ever get confused about how to select or how to create Arrays or Structs in BigQuery then you are at the right place. Arrays and Structs are confusing, and I won’t argue on that. But before I come to the confusing part, let me first tell you a little bit about what exactly each of them is and how they are different.

What are Arrays and how are they used in BigQuery:

Arrays in BigQuery, like in any other language, are a collection of elements of the same data type.

For example, this is what an Array address_history might look like:

id:”1",
name:”abc”,
age:”20",
address_history: [“current”, “previous”, “birth”]

What are Structs and how are they used in BigQuery:

A struct is a data type that has attributes in key-value pairs, just like a dictionary in Python.

Within each record, multiple attributes have their own values. These attributes can either be referred to as keys or Struct columns.

id:”1",
name:”abc”,
age:”20",
address_history: {
“status”:”current”,
“address”:”London”,
“postcode”:”ABC123D”
}