BigQuery STRUCT vs ARRAY: Differences and When to Use Each

I've previously did a short intro post about ARRAYS in BigQuery, but I do see from time to time people that are just getting started become confused about how are they different from STRUCTS and when should we use them.

Let's clarify this.

STRUCT = a bundle of "columns" inside a single column. STRUCTS fields have a mandatory data type (inferred or declared by you) and an optional name. You're still having one instance of each field, but this field can be an ARRAY of multiple things.

ARRAY = a bundle of "rows", a list inside a single row. They NEED to be of the same type - INT64, STRING, STRUCT etc. They cannot have another ARRAY directly under them, but you can get around that with an ARRAY(STRUCT([array_inside_struct])

You can still combine the two as you want, nest them in multiple layers, as long as you don't have an ARRAY directly under another ARRAY.

Both of them you can compare but you cannot order by or group by.
For STRUCT, you can totally ORDER BY or GROUP BY one of the fields in the STRUCT (as long as it's not a STRUCT or ARRAY itself).

When to use each? Let's look an example.

STRUCT = a bundle of fields that relate to the same "thing" - say your current address - city, street name, postal_code etc. Helps with a cleaner, more intuitive schema. TYPE = RECORD, MODE = NULLABLE

ARRAY = a list of things (0, 1 or more) that are related to this observation, for example a list of instruments a person plays on. TYPE = your_data_type, MODE = REPEATED

ARRAY of STRUCTS = you have a list of "things" that you know multiple things about and want to keep the together i.e. certifications => (name, from_date).
Would also be good to store all them addresses a person ever had. TYPE = RECORD, MODE = REPEATED

Found it useful? Check out to my Analytics newsletter at notjustsql.com.

Enjoyed this? Here are some related articles you might find useful:

Understanding STRUCTS in BigQuery

Comments

BigQuery Arrays & Structs

Using STRUCTS for quick analysis in BigQuery

More from this blog

pre_ and post_operations in Dataform

BigQuery Object Tables: A Practical Introduction

BigQuery BigLake Tables Explained: What They Are and When to Use Them

BigQuery Saves Your Query Results — Here's How to Find Them

Parameters in BigQuery

Command Palette

Comments

BigQuery Arrays & Structs

Using STRUCTS for quick analysis in BigQuery

More from this blog