Demystifying Differences Between ARRAYS and STRUCTS in BigQuery

In BigQuery, the difference between ARRAYS and STRUCTS is as follows:

• STRUCT: A STRUCT is like a bundle of "columns" inside a single column. Each field within a STRUCT has a mandatory data type and an optional name. A field within a STRUCT can be an ARRAY, but you're still having one instance of each field.

• ARRAY: An ARRAY is a bundle of "rows" or a list inside a single row. All elements in an ARRAY need to be of the same type, such as INT64, STRING, STRUCT, etc. You cannot have an ARRAY directly under another ARRAY, but you can have an ARRAY of STRUCTS which contains an array.

STRUCTS help create a cleaner and more intuitive schema, while ARRAYS are used for listing multiple items related to a single observation.

In BigQuery, STRUCTS and ARRAYS can be used in the following ways:

• STRUCTS: Use STRUCTS when you have a bundle of fields that relate to the same entity, such as an address with city, street name, and postal_code. STRUCTS are defined with TYPE = RECORD and MODE = NULLABLE.

• ARRAYS: Use ARRAYS when you have a list of items that are related to an observation, such as a list of instruments a person plays. ARRAYS are defined with TYPE = your_data_type and MODE = REPEATED.

• ARRAY of STRUCTS: Use an ARRAY of STRUCTS when you have a list of entities and you want to keep multiple attributes about them together, like a list of certifications with their names and from_date. This is also defined with TYPE = RECORD and MODE = REPEATED.

You can combine and nest STRUCTS and ARRAYS as needed, with the restriction of not having an ARRAY directly under another ARRAY.

In BigQuery, you can compare both STRUCTS and ARRAYS, but there are limitations when it comes to using ORDER BY or GROUP BY:

• For ARRAYS, you cannot use ORDER BY or GROUP BY directly on the ARRAY.

• For STRUCTS, you can ORDER BY or GROUP BY one of the fields within the STRUCT, provided that the field itself is not another STRUCT or ARRAY.