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

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