BigQuery Essentials: Building Data Structures with Struct, Array, and JSON for Optimal Performance

1. Introduction

If you're just getting started with BigQuery, you’ve probably noticed that it’s a bit different from SQL Server. Sure, they both use SQL-like syntax, but the way they handle data structures—especially complex ones—is where things really start to diverge. If you’re used to flat, relational data in SQL Server, BigQuery’s ability to handle nested and repeated data can feel like a game-changer.

BigQuery is designed for big data, and when you're working with massive datasets, you need more than just basic tables and rows. This is where Struct, Array, and JSON come into play. These structures allow you to group, nest, and repeat data, which can make your queries faster, more efficient, and easier to manage—especially when dealing with complex data relationships.

In this article, we'll introduce you to these three core data structures, show you how they work, and explain why they’re essential for getting the best performance out of BigQuery. Whether you’re coming from a SQL Server background or just learning your way around BigQuery, understanding these tools will give you a significant boost.

2. Overview of BigQuery's Data Structures

When you’re transitioning from SQL Server to BigQuery, one of the first things you’ll notice is how BigQuery handles data differently, especially when it comes to complex data types. In SQL Server, most of your work probably revolved around flat, relational tables. In contrast, BigQuery is built for handling hierarchical and nested data, which can save you from writing complex JOINs and make querying more efficient.

Let’s briefly break down the three key data structures we’ll be focusing on: Struct, Array, and JSON.

Struct: Grouping Related Data Together

Think of Struct as a container for grouping related fields into a single entity. For example, if you have customer data that includes name, address, and contact details, you can group all these fields into one Struct. Instead of having separate columns for each field, you can keep them together in a structured format, making it easier to manage and query.

Structs are great when you want to keep data logically grouped but still part of a single row. It’s similar to having a mini-table inside a single column.

Array: Storing Multiple Values in One Column

An Array is exactly what it sounds like—a list of values, all stored within a single column. This is super useful when you’re working with repeated data. For example, let’s say a customer has made multiple purchases. Instead of storing each purchase as a separate row or doing complex joins, you can store all the purchases as an array within the customer’s row.

Arrays allow you to avoid redundancy and can simplify how you structure your data. When querying, you can even use functions like UNNEST to flatten the array into individual rows, if needed.

JSON: Handling Semi-Structured Data

BigQuery supports JSON, which is a format you’re likely familiar with if you’ve worked with APIs or semi-structured data. JSON allows you to store data in a flexible, key-value format. It’s particularly useful when the schema isn’t fixed or if you need to handle variable data (e.g., event logs or external data sources).

With JSON, you can store complex, hierarchical data in a single column, and then extract or manipulate specific elements using built-in BigQuery functions like JSON_EXTRACT.

In SQL Server, you would typically use a combination of tables and JOINs to represent relationships between different data points. BigQuery, on the other hand, allows you to nest and repeat data within a single row using Struct, Array, and JSON. This is not only more intuitive for complex data relationships but can also lead to performance improvements by reducing the need for multiple table joins.

By understanding these data structures, you’ll be able to better organize and optimize your data in BigQuery, leading to faster queries and more efficient data storage. In the next sections, we’ll dive deeper into each structure and explore how to use them with practical examples.

3. Understanding Struct in BigQuery

Now that we’ve covered the basics, let’s dive into Struct, one of the most powerful and flexible data structures in BigQuery. If you’ve ever needed to group related data together in SQL Server, you might have used subqueries or complex joins. In BigQuery, Struct gives you a simpler and more efficient way to do this by allowing you to group fields into a single data type.

What is a Struct?

A Struct in BigQuery is essentially a container that can hold multiple fields—think of it as a record or object that groups related data together. Structs are great when you have multiple attributes that logically belong together, but you still want them to be part of the same row.

For example, imagine you’re working with customer data. A customer might have a name, address, and contact information. Instead of spreading this data across multiple columns, you can bundle it into a Struct so that all of this information stays neatly organized within a single column.

Here’s what a Struct looks like in BigQuery:

SELECT 
  STRUCT('John Doe' AS name, '123 Main St' AS address, '555-1234' AS phone) AS customer_info

In this example, we’re creating a Struct called customer_info that holds three fields: name, address, and phone. Instead of scattering these fields across different columns, they’re all neatly packaged in one place.

Why Use Structs?

Structs are useful for a few reasons:

Logical Grouping: They help you keep related data together. Instead of dealing with multiple columns for related fields, you can group them into a single structure.
Simplified Queries: When querying nested or hierarchical data, Structs make it easier to access specific fields without complex joins.
Improved Readability: Structs can make your data schema more readable and organized, especially when working with complex datasets.

How to Create and Use Structs

Let’s look at a more practical example. Imagine you have a table of employees, and each employee has personal details that you want to group together using a Struct.

SELECT 
  employee_id,
  STRUCT(first_name, last_name, birthdate) AS personal_details,
  department
FROM `project.dataset.employees`

In this query, we’re using a Struct to group the employee’s first name, last name, and birthdate into a single field called personal_details. Now, instead of having three separate columns for these attributes, we have one structured column that holds all the relevant information.

Accessing Data Inside a Struct

So, how do you query specific fields inside a Struct? It’s simple! You can access the fields using dot notation.

For example, if you want to retrieve just the employee’s first name from the personal_details Struct, you can write:

SELECT 
  employee_id,
  personal_details.first_name,
  department
FROM `project.dataset.employees`

This allows you to pull out the specific field you need while keeping the rest of the data structured.

Nesting Structs

What’s even more powerful is that Structs can contain other Structs. You can nest them to represent more complex hierarchies. For example, let’s say an employee has both personal and contact information that you want to store separately within a Struct:

SELECT 
  employee_id,
  STRUCT(first_name, last_name, birthdate, 
         STRUCT(email, phone) AS contact_info) AS personal_details,
  department
FROM `project.dataset.employees`

In this example, the personal_details Struct contains another Struct, contact_info, which holds the employee’s email and phone number. This kind of nesting is incredibly useful when you’re dealing with deeply hierarchical data.

Structs in BigQuery provide a flexible and efficient way to group related data, making queries more organized and improving performance. They eliminate the need for complex joins and help manage nested data more easily. In the next section, we’ll explore Array, another powerful data structure that lets you store multiple values in a single column. Stay tuned!

4. Leveraging Array in BigQuery

After getting familiar with Struct, let’s move on to another versatile data structure in BigQuery: the Array. If you’ve ever needed to store multiple values for a single attribute in SQL Server, you may have used separate rows or related tables. In BigQuery, Array simplifies this by letting you store multiple values within a single column. This structure can help you streamline your queries and avoid repetitive data entry.

What is an Array?

An Array in BigQuery is a list of values of the same data type that are stored in a single column. This can be extremely useful when you have repeated data that belongs to a single entity. For example, if a customer has made multiple purchases, you can store all of those purchases in an Array within that customer's row rather than creating multiple rows or using a complex join structure.

Here’s a simple example of creating an array of purchase amounts for a single customer:

SELECT 
  'John Doe' AS customer_name,
  [25.50, 49.99, 19.99] AS purchase_amounts

In this example, we’re using an Array to store three purchase amounts in a single column for the customer "John Doe." Each element in the array is of the same type (numeric in this case), which is a requirement for Arrays in BigQuery.

Why Use Arrays?

Arrays come in handy in several scenarios:

Efficient Data Storage: Instead of creating multiple rows for repeated data (like multiple purchases), you can store it in a single column, reducing redundancy.
Simplified Querying: Arrays allow you to avoid joins when working with repeated data. You can keep related data together in one row, which can make your queries more straightforward.
Flexible Data Handling: Arrays allow for flexible queries and manipulation. You can filter, sort, and even expand the array elements into individual rows when needed.

Creating and Using Arrays

Let’s say you’re working with a table of orders, and each customer can have multiple items in their order. You can store these items in an array, like this:

SELECT 
  order_id,
  customer_name,
  ARRAY['item1', 'item2', 'item3'] AS items
FROM `project.dataset.orders`

In this example, each order has an array of items. Instead of having separate rows for each item, they’re stored in a single array within the row for that order.

Accessing Data Inside an Array

Just like with Structs, you can access the elements of an array. To retrieve a specific element from the array, you can use ARRAY[offset] syntax, where offset is the index number (starting from 0). For example, to get the first item from the array:

SELECT 
  order_id,
  items[OFFSET(0)] AS first_item
FROM `project.dataset.orders`

In this query, we’re pulling the first item from the items array. You can also use ORDINAL (which starts from 1) if you prefer 1-based indexing.

Unnesting Arrays

One of the most powerful features of arrays is the ability to “unnest” them—essentially flattening the array into individual rows. This is useful when you want to treat each element of the array as its own row, especially for analysis or reporting.

Here’s how you can unnest an array:

SELECT 
  order_id,
  item
FROM `project.dataset.orders`, UNNEST(items) AS item

In this query, we’re using the UNNEST function to expand the items array into separate rows. If an order has three items, it will now return three rows—one for each item.

Working with Arrays in Real-World Scenarios

Arrays are particularly useful in scenarios where data naturally repeats. For example, in event tracking, a user might perform multiple actions during a session. Instead of creating multiple rows for each action, you can store all actions in an array and then use UNNEST when you need to analyze each action separately.

Here’s an example of storing multiple user actions in an array:

SELECT 
  user_id,
  ARRAY['click', 'scroll', 'purchase'] AS actions
FROM `project.dataset.user_activity`

When you need to analyze individual actions, you can use UNNEST to break the array down into individual events:

SELECT 
  user_id,
  action
FROM `project.dataset.user_activity`, UNNEST(actions) AS action

Arrays provide a flexible and efficient way to handle repeated data in BigQuery. Instead of creating multiple rows or tables for related values, you can use an array to keep data together and simplify your queries. Next up, we’ll explore JSON, another powerful tool for working with semi-structured data in BigQuery.

5. Managing Semi-Structured Data with JSON in BigQuery

The last key data structure we’ll cover is JSON. If you’re already familiar with working with APIs or semi-structured data, you’ll know that JSON (JavaScript Object Notation) is a flexible format for storing and exchanging data. BigQuery supports JSON natively, allowing you to handle complex, nested data directly in your queries without the need for external transformation tools.

What is JSON in BigQuery?

In BigQuery, you can store data in JSON format within a column, which gives you the flexibility to manage semi-structured or variable data. Unlike Struct and Array, where you define specific fields or types, JSON allows for more flexibility because it doesn’t require a rigid schema. This makes it perfect for use cases where the structure of the data might change or where data arrives from various external sources in a dynamic format.

For example, here’s what a JSON object might look like in BigQuery:

SELECT 
  '{"name": "John Doe", "age": 30, "contact": {"email": "[email protected]", "phone": "555-1234"}}' AS customer_data

This JSON object contains information about a customer, including nested contact details. Unlike a Struct, which requires predefined field names, JSON allows for arbitrary key-value pairs.

Why Use JSON?

JSON is especially useful in scenarios like:

Handling Dynamic or Semi-Structured Data: When data structures are not fixed (e.g., logs, event data, or external API data), JSON can store it all without requiring changes to your schema.
External Data Integration: If you’re pulling data from sources like web services, APIs, or cloud platforms, JSON often comes as the default data format. BigQuery can handle it directly, making it easy to load and query.
Hierarchical Data Representation: JSON can handle deeply nested data structures efficiently, similar to Struct, but with more flexibility when the structure of the data is not strictly known in advance.

Storing and Querying JSON Data

Let’s say you have a table of customer interactions, and each interaction is logged as a JSON object. Here’s an example of how you might store and query this data:

SELECT 
  '{"interaction_type": "purchase", "amount": 49.99, "details": {"product_id": "123", "quantity": 2}}' AS interaction_data

This JSON object stores details about a purchase, including the product ID and quantity.

Extracting Data from JSON

While JSON gives you flexibility, querying it requires a different approach compared to Struct or Array. To extract values from a JSON object, BigQuery provides functions like JSON_EXTRACT and JSON_EXTRACT_SCALAR.

For example, if you want to extract the interaction_type from the JSON object, you would write:

SELECT 
  JSON_EXTRACT(interaction_data, '$.interaction_type') AS interaction_type
FROM `project.dataset.customer_interactions`

This query extracts the interaction_type field from the JSON object. If you want to extract scalar values (like strings or numbers), you would use JSON_EXTRACT_SCALAR:

SELECT 
  JSON_EXTRACT_SCALAR(interaction_data, '$.details.product_id') AS product_id
FROM `project.dataset.customer_interactions`

In this case, we’re pulling out the product ID from the nested details object.

Handling Nested JSON Data

JSON in BigQuery can also handle deeply nested structures. You can keep drilling down into these nested objects using the appropriate path expressions (like $.details.product_id), allowing you to work with complex data hierarchies without needing to flatten everything into separate fields or tables.

Real-World Use Case: Event Logs

One common use case for JSON in BigQuery is storing and querying event logs from web or mobile applications. Each event (e.g., click, scroll, purchase) can be stored as a JSON object that contains details about the user’s interaction, device, and session.

Here’s an example of storing an event log in JSON format:

SELECT 
  '{"event": "click", "user": {"id": "abc123", "location": "US"}, "metadata": {"page": "/home", "time": "2024-10-13T14:22:00Z"}}' AS event_log

This JSON object contains the event type, user information, and metadata like the page and timestamp.

To query specific fields from the event log, you could use:

SELECT 
  JSON_EXTRACT_SCALAR(event_log, '$.event') AS event_type,
  JSON_EXTRACT_SCALAR(event_log, '$.user.id') AS user_id,
  JSON_EXTRACT_SCALAR(event_log, '$.metadata.page') AS page
FROM `project.dataset.event_logs`

This allows you to pull specific details from the nested JSON structure for analysis.

When to Use JSON vs Struct/Array

While JSON is great for handling dynamic or semi-structured data, it’s important to recognize when it’s better to use Struct or Array. Use Struct and Array when your data has a known structure, and you want the added benefits of schema enforcement and easier querying. On the other hand, use JSON when your data is more dynamic or comes from external sources that might have varying schemas.

By using JSON in BigQuery, you can easily handle semi-structured data without needing to transform it into a rigid schema first. This flexibility is ideal for modern, cloud-native applications where data is often varied and hierarchical. In the next section, we’ll wrap things up by discussing how to combine Struct, Array, and JSON for optimal performance in your BigQuery projects.

6. Optimizing Performance with Struct, Array, and JSON in BigQuery

Now that you’ve got a good understanding of Struct, Array, and JSON, the final step is learning how to use these data structures effectively to optimize performance in BigQuery. While they provide flexibility and simplify data organization, you need to apply them thoughtfully to ensure your queries run efficiently, especially when working with large datasets.

1. Structs for Logical Grouping

When using Struct, one of the key benefits is reducing the complexity of your schema by grouping related fields together. However, overusing Structs or nesting them too deeply can hurt query performance, especially if you frequently access only part of the structure.

Tips for Optimizing Structs:

Avoid deep nesting: The deeper the structure, the more processing time required to access fields. Keep nesting to a reasonable level.
Flatten when necessary: If your queries often extract a few fields from a deeply nested Struct, consider flattening your data model. This can reduce the complexity of your queries and speed up access times.
Use only what’s necessary: When selecting from a Struct, query only the fields you need. Accessing unnecessary fields can increase I/O operations and slow down your queries.

For example, instead of querying an entire customer_info Struct:

SELECT customer_info FROM `project.dataset.customers`

You can access only the fields you need:

SELECT customer_info.name, customer_info.email FROM `project.dataset.customers`

This reduces the amount of data BigQuery needs to process.

2. Arrays for Repeated Data

Arrays are perfect for managing repeated data, but similar to Structs, they can slow down performance if overused or not handled properly. Large arrays or frequent UNNEST operations can increase query times, especially with massive datasets.

Tips for Optimizing Arrays:

Limit the array size: Avoid storing extremely large arrays in a single row. Large arrays can lead to higher memory usage and slower query execution.
Filter arrays before unnesting: If you need to unnest an array, try to filter out unnecessary data before performing the UNNEST operation. This reduces the number of rows created by the unnesting process.
Use ARRAY_LENGTH: When working with large arrays, you can check the length before unnesting, allowing you to skip unnecessary processing.

Here’s how you can filter before unnesting:

SELECT order_id, item
FROM `project.dataset.orders`, UNNEST(items) AS item
WHERE ARRAY_LENGTH(items) > 0

This way, you’re ensuring that you only unnest arrays that actually contain data, saving on query cost and time.

3. JSON for Flexible Data Storage

While JSON provides incredible flexibility, its schema-less nature can sometimes lead to inefficiencies. JSON requires more processing power to extract fields compared to Struct or Array, so it’s important to use it only when necessary.

Tips for Optimizing JSON:

Use Struct when possible: If your data has a known structure, it’s better to use Struct instead of JSON. This allows BigQuery to optimize queries more efficiently.
Extract only the fields you need: JSON objects can be large, so avoid extracting the entire object if you only need specific fields.
Avoid over-relying on JSON_EXTRACT: Repeated calls to JSON_EXTRACT or JSON_EXTRACT_SCALAR can slow down your queries. Consider transforming JSON data into Structs or Arrays if you find yourself frequently querying the same fields.

Here’s an example where we only extract specific fields from a JSON object instead of the entire object:

SELECT 
  JSON_EXTRACT_SCALAR(event_log, '$.user.id') AS user_id,
  JSON_EXTRACT_SCALAR(event_log, '$.event') AS event_type
FROM `project.dataset.event_logs`

By narrowing down the fields, you’re reducing the amount of data BigQuery processes, which leads to faster query execution.

4. Combining Struct, Array, and JSON

In many cases, you’ll find yourself needing to combine these structures to represent complex data. While this is powerful, it’s important to balance complexity with performance. A deep mix of Structs, Arrays, and JSON can lead to difficult-to-read queries and slower performance if not handled properly.

Best Practices for Combining Structures:

Start with Structs: If your data has a known schema, use Structs to group related fields together. Structs are easier to query and more efficient than JSON for structured data.
Use Arrays for repeated data: If any of the fields in your Structs or JSON are lists, consider using Arrays to store them. This keeps related data organized and reduces the need for repeated joins.
Keep JSON for dynamic or external data: When working with data that doesn’t have a fixed schema (e.g., external API data), JSON is a great choice. However, if you find that your JSON structure stabilizes over time, consider moving it into Structs and Arrays for better performance.

5. Partitioning and Clustering

Another way to optimize performance when using Struct, Array, and JSON is through partitioning and clustering. These techniques allow BigQuery to process only the data that’s relevant to your query, reducing cost and execution time.

Tips for Partitioning and Clustering:

Partition your data: If you’re working with large datasets, partition your data by a commonly filtered field (e.g., date). This ensures that BigQuery only reads the relevant partitions, speeding up your queries.
Cluster by frequently queried fields: If you frequently filter by fields within Structs or JSON objects, consider clustering your data by those fields. This helps BigQuery locate the relevant data faster.

Here’s an example of partitioning by date and clustering by a nested field:

CREATE TABLE `project.dataset.orders`
PARTITION BY DATE(order_date)
CLUSTER BY customer_info.name AS
SELECT * FROM `project.dataset.raw_orders`

By partitioning and clustering your data, you’re ensuring that BigQuery reads and processes data more efficiently.

Conclusion: Struct, Array, and JSON are incredibly powerful tools for managing complex data in BigQuery. When used thoughtfully, they allow you to simplify your data models, streamline queries, and ultimately optimize performance. Just remember to keep things as simple as possible—deep nesting and overuse of JSON can slow things down. By following best practices and leveraging partitioning and clustering, you can unlock the full potential of BigQuery’s data structures.

7. Conclusion: Struct, Array, and JSON - When and How to Use Each

To wrap things up, let’s recap the key takeaways for using Struct, Array, and JSON effectively in BigQuery. Each of these data structures serves a specific purpose, and understanding when to use each is crucial for optimizing performance and making your queries more efficient.

Struct: Best for Grouping Related Fields

Use Struct when you need to logically group related fields in your data. It’s excellent for organizing your schema and simplifying query writing.
Keep nesting shallow to avoid complex queries and performance issues.
Access only the fields you need to minimize query costs.

Array: Best for Handling Repeated Data

Arrays are perfect when you’re dealing with repeated data, like a list of items in an order.
Use UNNEST cautiously, as it can quickly lead to large, slow queries if not handled carefully.
Always filter and reduce the size of arrays when possible before unnesting.

JSON: Best for Semi-Structured or Dynamic Data

JSON is the go-to for handling semi-structured data or when integrating external data sources like APIs.
While flexible, JSON can be more costly to query, so only use it when your data doesn’t have a fixed schema.
Extract specific fields instead of querying entire JSON objects to keep performance in check.

Combining Struct, Array, and JSON

You don’t have to pick just one; often, the best solution is a combination of these data structures.
Use Struct for organizing your data, Array for repeated values, and JSON for more flexible or external data.
Be mindful of query complexity—combining too many layers of nesting or data types can make queries difficult to read and slow to execute.

Key Performance Tips

Avoid deep nesting in both Struct and JSON fields.
Use partitioning and clustering to optimize how BigQuery reads and processes data.
Apply best practices such as filtering before unnesting, and leverage built-in functions like ARRAY_LENGTH and JSON_EXTRACT to extract only the data you need.

By mastering these data structures, you’ll not only make your data models cleaner and more organized but also boost your query performance, making your work in BigQuery faster and more efficient. Structs, Arrays, and JSON are the building blocks of efficient data handling in BigQuery, and knowing when and how to use each will set you up for success as you work with larger, more complex datasets.

So, the next time you’re modeling data in BigQuery, remember these tips, and start structuring your data with optimal performance in mind!

The Tech Trail