Skip to content

Undertand STRUCT In BigQuery

STRUCT

In BigQuery, a STRUCT is a data type that represents a structured record. You can group multiple fields together into a single field. It is useful for organizing and working with complex or nested data.

You can consider a STRUCT conceptually like a separate table that is already pre-joined into your main table. A STRUCT can have:

  • One or many fields in it
  • The same or different data types for each field
  • It’s own alias
STRUCT<
  product_id INT64,
  product_name STRING,
  category STRING,
  price FLOAT64,
  in_stock BOOL
>

In this example, the struct contains five fields and multiple data types.

Create one STRUCT

SELECT  STRUCT( 101 AS id,
                "apple" AS name, 
                "fruit" AS category,
                [1.0,1.1,1.3,0.8,1.25] AS price,
                TRUE AS in_stock)  AS  product
Rowproduct.idproduct.nameproduct.categoryproduct.pricein_stock
1101applefruit1.0true
1.1
1.3
0.8
1.25

Create multiple STRUCTs

WITH products AS (
  SELECT 
    [STRUCT(101 AS id, "apple" AS name, "fruit" AS category, [1.0, 1.1, 1.3, 0.8, 1.25] AS price, TRUE AS in_stock),
     STRUCT(102 AS id, "banana" AS name, "fruit" AS category, [0.8, 1.0, 0.9, 0.5, 1.1] AS price, TRUE AS in_stock),
     STRUCT(103 AS id, "kiwi" AS name, "fruit" AS category, [2.0, 1.9, 1.8, 1.8, 1.95] AS price, FALSE AS in_stock)
    ] AS products
)
Rowproducts.idproducts.nameproducts.categoryproducts.pricein_stock
1101applefruit1.0true
1.1
1.3
0.8
1.25
102bananafruit0.8true
1.0
0.9
0.5
1.1
103kiwifruit2.0false
1.9
1.8
1.8
1.95

Extract Data from STRUCT

In the SELECT statement, we extract information from each product using dot notation (product.id, product.name, etc.).

The UNNEST function is used to unnest the array of products, making each product element accessible in the query.

-- Query the structured data
SELECT 
  product.id AS id,
  product.name AS name,
  product.category AS category,
  product.price AS price,
  product.in_stock AS in_stock
FROM 
  products,
  UNNEST(products.products) AS product
  WHERE name = "banana"
Rowidnamecategorypricein_stock
1102bananafruit0.8true
1.0
0.9
0.5
1.1

We also can find the best( lowest) price for customers.

  • The UNNEST function is used to flatten the array of prices for each product.
  • Inside the subquery, MIN(price) calculates the minimum price among the flattened prices for each product.

-- Query the structured data
SELECT 
  product.name AS name,
  (SELECT MIN(price) FROM UNNEST(product.price) AS price) AS best_price,
  product.in_stock AS in_stock
FROM 
  products,
  UNNEST(products.products) AS product;
Rownamebest_pricein_stock
1apple0.8true
2banana0.5true
3kiwi1.8false