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
Row | product.id | product.name | product.category | product.price | in_stock |
---|---|---|---|---|---|
1 | 101 | apple | fruit | 1.0 | true |
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
)
Row | products.id | products.name | products.category | products.price | in_stock |
---|---|---|---|---|---|
1 | 101 | apple | fruit | 1.0 | true |
1.1 | |||||
1.3 | |||||
0.8 | |||||
1.25 | |||||
102 | banana | fruit | 0.8 | true | |
1.0 | |||||
0.9 | |||||
0.5 | |||||
1.1 | |||||
103 | kiwi | fruit | 2.0 | false | |
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"
Row | id | name | category | price | in_stock |
---|---|---|---|---|---|
1 | 102 | banana | fruit | 0.8 | true |
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;
Row | name | best_price | in_stock |
---|---|---|---|
1 | apple | 0.8 | true |
2 | banana | 0.5 | true |
3 | kiwi | 1.8 | false |