Google BigQuery provides sophisticated ways to manage complex data structures, such as arrays and structs.

Understanding Arrays in BigQuery #

An array in BigQuery is an ordered list containing zero or more elements of a particular data type.

Creating Arrays #

Arrays are created by enclosing a comma-separated list of elements within square brackets ([]). For example:

SELECT ["apple", "banana", "cherry"] AS fruit_array

Querying Arrays #

To access elements from an array, use the OFFSET function. Remember, array indexing in BigQuery starts from zero. For example, to access the first element from the fruit array, use OFFSET(0).

SELECT fruit_array[OFFSET(0)]
FROM `project.dataset.table`

Understanding Structs in BigQuery #

A struct (also called a record) is a complex data type that encapsulates a set of fields. Each field has a name and data type.

Creating Structs #

To create a struct, use the STRUCT function. For instance:

SELECT STRUCT("John" AS name, 30 AS age) AS person

Querying Structs #

To access elements from a struct, use dot notation (.). For instance, to access the name field from the person struct, you'd write:

SELECT person.name
FROM `project.dataset.table`

Combining Arrays and Structs #

Combining arrays and structs can help manage complex data types. For instance, you can define an array of structs, which is useful for storing repeated fields.

Creating Array of Structs #

SELECT ARRAY<STRUCT<name STRING, age INT64>>
[("John", 30), ("Jane", 25)] AS people

Querying Array of Structs #

Querying an array of structs involves specifying the element's index and the desired field. For instance, to access name of the first person, you'd write:

SELECT people[OFFSET(0)].name
FROM `project.dataset.table`

Understanding how to create and query arrays and structs in BigQuery allows you to manipulate complex data types and achieve more flexibility in your data analysis tasks.