Below is for BigQuery Standard SQL

#standardSQL
SELECT 
  ARRAY(
    SELECT AS STRUCT item, unit_cost, quantity
    FROM UNNEST(costs.item) item WITH OFFSET
    LEFT JOIN UNNEST(costs.unit_cost) unit_cost WITH OFFSET USING(OFFSET)
    LEFT JOIN UNNEST(costs.quantity) quantity WITH OFFSET USING(OFFSET)
  ) AS costs
FROM `project.dataset.table`   

if to apply to sample data from your question - result is (in JSON View)

[
  {
    "costs": [
      {
        "item": "cheese",
        "unit_cost": "2",
        "quantity": "1"
      },
      {
        "item": "ham",
        "unit_cost": "5",
        "quantity": "2"
      },
      {
        "item": "salad",
        "unit_cost": "8",
        "quantity": "1"
      }
    ]
  }
]
Answer from Mikhail Berlyant on Stack Overflow
🌐
Reddit
reddit.com › r/bigquery › array of structs vs struct of arrays
r/bigquery on Reddit: ARRAY of STRUCTS vs STRUCT of ARRAYS
September 8, 2024 -

Hi,

So I'm trying to learn the concept of STRUCTS, ARRAYS and how to use them.

I asked AI to create two sample tables: one using ARRAY of STRUCTS and another using STRUCT of ARRAYS.

This is what it created.

ARRAY of STRUCTS:

STRUCT of ARRAYS:

When it comes to this table- what is the 'correct' or 'optimal' way of storing this data?

I assume that if purchases is a collection of information about purchases (which product was bought, quantity and price) then we should use STRUCT of ARRAYS here, to 'group' data about purchases. Meaning, purchases would be the STRUCT and product_names, prices, quantities would be ARRAYS of data.

In such example- is it even logical to use ARRAY of STRUCTS? What if purchases was an ARRAY of STRUCTS inside. It doesn't really make sense to me here.

This is the data in both of them:

I guess ChatGPT brought up a good point:

"Each purchase is an independent entity with a set of associated attributes (e.g., product name, price, quantity). You are modeling multiple purchases, and each purchase should have its attributes grouped together. This is precisely what an Array of Structs does—it groups the attributes for each item in a neat, self-contained way.

If you use a Struct of Arrays, you are separating the attributes (product name, price, quantity) into distinct arrays, and you have to rely on index alignment to match them correctly. This is less intuitive for this case and can introduce complexities and potential errors in querying."

Top answer
1 of 5
3
Fun question. I think chatgpt is right. When it comes to extracting the data an array of structs will ensure a single struct within the array has the correct data sitting "on the same line". There could be use cases where null values in an array may be ignored or break iteration based processing if they aren't handled properly. It also has the potential for population to put things in the wrong order if it's not specified. No idea if one way is faster than another, though. Id feel doing a struct of arrays would have to be a lot faster for me to consider doing it that way, and if I did I'd try to keep it isolated from anything else.
2 of 5
3
ChatGPT is totally correct (in this case). An “array of structs” is an intrinsically useful structure. As an exercise, consider each struct as a form, like an employment application, and an array as a folder. Putting a bunch of structs into an array makes total sense, since you’re keeping each application intact, and grouping them. That’s why an “Array of Structs” is the most common user-enumerated data structure you’ll find. It’s even more useful when you consider that you can order the records within a given array, so you can enable logical assumptions about the first element being the earliest, smallest…whatever. A “Struct of Arrays”, though, is like taking each form, cutting it apart into its component fields…and then putting all the name slips into one container, all the address slips into a different container, and all the previous employment slips in a third. You’d need to have a whole separate system in place just to keep track of exactly which slips of paper came from which original forms. That’s what ChatGPT is referring to as “index alignment”. So one variation has clear implications that make it useful and common, while the other has implications that make it complicated, easy to screw up, and rare. That doesn’t mean that you’d never want to create a struct of arrays, but you’d want to have a very specific logical reason why that’s the most appropriate structure. Arrays of structs, however, are basically a “best-practice pattern” in BigQuery.
🌐
Hevo
hevodata.com › home › learn › bigquery
Understanding BigQuery Structs with Syntax and Examples
January 9, 2026 - In BigQuery, STRUCTs are complex data types that allow you to group multiple fields into a single record. A STRUCT is like a row in a table, where each field can have a different data type. ARRAY: An ordered list of values of the same data type ...
People also ask

What is the difference between an ARRAY and a STRUCT in BigQuery?
ARRAY: An ordered list of values of the same data type (e.g., an array of integers or strings).
STRUCT: A record that can contain multiple fields of different data types (e.g., string, int).
🌐
hevodata.com
hevodata.com › home › learn › bigquery
Understanding BigQuery Structs with Syntax and Examples
What are structs in BigQuery?
In BigQuery, STRUCTs are complex data types that allow you to group multiple fields into a single record. A STRUCT is like a row in a table, where each field can have a different data type.
🌐
hevodata.com
hevodata.com › home › learn › bigquery
Understanding BigQuery Structs with Syntax and Examples
Is BigQuery structured or unstructured?
BigQuery is primarily designed to handle structured and semi-structured data (e.g., JSON). It’s optimized for working with tabular data but can also process nested and repeated fields such as JSON and ARRAY/STRUCT types. While it can process some semi-structured data, it is not ideal for handling fully unstructured data like text or images.
🌐
hevodata.com
hevodata.com › home › learn › bigquery
Understanding BigQuery Structs with Syntax and Examples
🌐
The Digital Skye
thedigitalskye.com › 2021 › 01 › 21 › explore-arrays-and-structs-for-better-performance-in-google-bigquery
Explore Arrays and Structs for Better Query Performance in Google BigQuery – The Digital Skye
January 21, 2021 - Let’s save the result of the above query into a BigQuery table named “restaurant_cuisine_unnested”. Next, we will use the ARRAY_AGG operator with GROUP BY to bring it back to the array format. # Aggregate elements into arrays with ARRAY_AGG #standardSQL SELECT name, location, ARRAY_AGG(cuisine ORDER BY cuisine) AS cuisine_array FROM `array-and-struct.array.restaurant_cuisine_unnested` GROUP BY name, location;
Top answer
1 of 2
6

Below is for BigQuery Standard SQL

#standardSQL
SELECT 
  ARRAY(
    SELECT AS STRUCT item, unit_cost, quantity
    FROM UNNEST(costs.item) item WITH OFFSET
    LEFT JOIN UNNEST(costs.unit_cost) unit_cost WITH OFFSET USING(OFFSET)
    LEFT JOIN UNNEST(costs.quantity) quantity WITH OFFSET USING(OFFSET)
  ) AS costs
FROM `project.dataset.table`   

if to apply to sample data from your question - result is (in JSON View)

[
  {
    "costs": [
      {
        "item": "cheese",
        "unit_cost": "2",
        "quantity": "1"
      },
      {
        "item": "ham",
        "unit_cost": "5",
        "quantity": "2"
      },
      {
        "item": "salad",
        "unit_cost": "8",
        "quantity": "1"
      }
    ]
  }
]
2 of 2
1

You can use below query:

with data as (
select STRUCT<item ARRAY<STRING>, unit_cost ARRAY<INT64>, quantity ARRAY<INT64>>
  (['cheese', 'ham', 'salad'], [2, 5, 8], [1, 2, 1]) entry
union all
select (['othercheese', 'otherham', 'othersalad'], [3, 8, 10], [11, 22, 11])
union all
select (['othercheese', 'otherham', 'othersalad'], [3, 8, 10], [11, 22, 11])
) 
SELECT ARRAY_AGG(STRUCT(item, unit_cost, quantity))
FROM data, UNNEST(entry.item) item WITH OFFSET
    LEFT JOIN UNNEST(entry.unit_cost) unit_cost WITH OFFSET USING(OFFSET)
    LEFT JOIN UNNEST(entry.quantity) quantity WITH OFFSET USING(OFFSET)

Output

[
  {
    "f0_": [
      {
        "item": "cheese",
        "unit_cost": "2",
        "quantity": "1"
      },
      {
        "item": "ham",
        "unit_cost": "5",
        "quantity": "2"
      },
      {
        "item": "salad",
        "unit_cost": "8",
        "quantity": "1"
      },
      {
        "item": "othercheese",
        "unit_cost": "3",
        "quantity": "11"
      },
      {
        "item": "otherham",
        "unit_cost": "8",
        "quantity": "22"
      },
      {
        "item": "othersalad",
        "unit_cost": "10",
        "quantity": "11"
      },
      {
        "item": "othercheese",
        "unit_cost": "3",
        "quantity": "11"
      },
      {
        "item": "otherham",
        "unit_cost": "8",
        "quantity": "22"
      },
      {
        "item": "othersalad",
        "unit_cost": "10",
        "quantity": "11"
      }
    ]
  }
]
🌐
Medium
medium.com › data-engineers-notes › understanding-structs-in-bigquery-1ebe29f82ae9
Understanding STRUCTS in BigQuery | by Constantin Lungu | Data Engineer’s Notes | Medium
April 19, 2024 - STRUCTS fields have a mandatory ... each field, but this field can be an ARRAY of multiple things. ARRAY = a bundle of “rows”, a list inside a single row....
🌐
Medium
medium.com › google-cloud › how-to-work-with-array-and-structs-in-bigquery-9c0a2ea584a6
How to work with Arrays and Structs in Google BigQuery | by Deepti Garg | Google Cloud - Community | Medium
March 2, 2021 - For example, this is what an Array ... [“current”, “previous”, “birth”] A struct is a data type that has attributes in key-value pairs, just like a dictionary in Python....
🌐
Google
docs.cloud.google.com › bigquery › work with arrays
Work with arrays | BigQuery | Google Cloud Documentation
In GoogleSQL for BigQuery, an array ... data type, such as INT64, or a complex data type, such as STRUCT. However, arrays of arrays aren't supported....
Find elsewhere
🌐
Google Skills
skills.google › focuses › 3696
Working with JSON, Arrays, and Structs in BigQuery | Google Skills
December 16, 2025 - Just as ARRAY values give you the flexibility to go deep into the granularity of your fields, another data type allows you to go wide in your schema by grouping related fields together.
🌐
DEV Community
dev.to › stack-labs › how-to-pass-an-array-of-structs-in-bigquerys-parameterized-queries-39nm
How to pass an Array of Structs in Bigquery's parameterized queries - DEV Community
October 15, 2024 - ValueError: Missing detailed struct item type info for an empty array, please provide a StructQueryParameterType instance. The error message is pretty clear: "RECORD" is not enough for Bigquery to know what to do with your empty array. It needs the fully detailed structure.
🌐
Shotlefttodatascience
shotlefttodatascience.com › 2019 › 12 › 27 › tutorial-bigquery-arrays-and-structs
Tutorial: BigQuery arrays and structs – Sho't left to data science
January 5, 2025 - So, having seen how we construct and extract array and struct data, let’s have a look at a typical dataset that has been set up using these features: `bigquery-public-data.the_met.vision_api_data`. Remember this is a public dataset so you’ll be able to query it too…
🌐
GitHub
gist.github.com › savelee › c42abc2920aaedf482dbe1cfe79a76c0
BigQuery Array Struct example · GitHub
BigQuery Array Struct example. GitHub Gist: instantly share code, notes, and snippets.
🌐
Towards Data Science
towardsdatascience.com › home › latest › save time and money in bigquery by using arrays and structs
Save Time and Money in BigQuery by Using Arrays and Structs | Towards Data Science
January 29, 2025 - A STRUCT can contain various data types whereas an ARRAY can only contain one. There are a few ways you can create an ARRAY in BigQuery, I will cover two of these methods in this article (helpful documentation here if you want to explore further).
🌐
Google Cloud
cloud.google.com › bigquery › array functions
Array functions | BigQuery | Google Cloud Documentation
SELECT ARRAY (SELECT AS STRUCT 1, 2, 3 UNION ALL SELECT AS STRUCT 4, 5, 6) AS new_array; /*------------------------+ | new_array | +------------------------+ | [{1, 2, 3}, {4, 5, 6}] | +------------------------*/
🌐
Swipe Insight
web.swipeinsight.app › posts › demystifying-differences-between-arrays-and-structs-in-bigquery-4313
Demystifying Differences Between ARRAYS and STRUCTS in BigQuery | Swipe Insight
April 16, 2024 - In BigQuery, STRUCTS are bundles of columns within a single column with a mandatory data type and can contain an ARRAY. ARRAYS are bundles of rows within a single row of the same type.
🌐
Towards Data Science
towardsdatascience.com › home › latest › explore arrays and structs for better performance in google bigquery
Explore Arrays and Structs for Better Performance in Google BigQuery | Towards Data Science
March 5, 2025 - If the Mode is REPEATED, that’s an array (a.k.a. repeated field). When the Type is RECORD, it represents a struct (a.k.a. nested field). ... Given the above table in BigQuery, it’s time to push ourselves a little bit harder.
🌐
Hevo
hevodata.com › home › learn › bigquery
BigQuery Array Functions and Examples Simplified | Hevo Data
January 9, 2026 - It allows you to store multiple ... difference is that an array stores multiple values of the same type (like numbers or strings), while a STRUCT can hold different types of data in a single field....