Creating a query based on record type data field in Google BigQuery

In Google BigQuery, the RECORD type (also known as STRUCT) is a complex data type that allows you to store nested and repeated fields within a table. It helps in organizing hierarchical or structured data efficiently.

Key Features of RECORD Type

1. Nested Fields (STRUCTs)
   • A RECORD can contain multiple subfields, each with its own data type.
   • It is similar to an object or dictionary in programming.
2. Repeated Fields (Arrays of STRUCTs)
   • A RECORD field can be repeated, meaning it behaves like an array of structures.
   • Useful for handling one-to-many relationships.
3. Improves Query Performance
   • Helps in reducing joins since related data is stored together.
   • Allows for efficient querying of nested data.

Example 1: Nested RECORD (STRUCT)

Let's define a table where a customer has an address as a nested field.

Schema Definition

Creating the Table

CREATE TABLE my_dataset.customers (
  customer_id STRING,
  name STRING,
  address STRUCT<
    street STRING,
    city STRING,
    zip STRING
  >
);

Inserting Data

INSERT INTO my_dataset.customers (customer_id, name, address)
VALUES 
('C123', 'Alice', STRUCT('123 Main St', 'New York', '10001'));

Querying Nested Data

SELECT customer_id, name, address.city FROM my_dataset.customers;

Example 2: Repeated RECORD (Array of STRUCTs)

Now, let's say a customer can have multiple orders.

Schema Definition

Creating the Table

CREATE TABLE my_dataset.customers_orders (
  customer_id STRING,
  name STRING,
  orders ARRAY<STRUCT<
    order_id STRING,
    product STRING,
    amount FLOAT64
  >>
);

Inserting Data

INSERT INTO my_dataset.customers_orders (customer_id, name, orders)
VALUES 
('C123', 'Alice', 
  [STRUCT('O001', 'Laptop', 1200.50), STRUCT('O002', 'Mouse', 25.00)]
);

Querying Repeated Fields (Flattening the Array)

SELECT customer_id, name, order.order_id, order.product, order.amount
FROM my_dataset.customers_orders, UNNEST(orders) AS order;

• UNNEST(orders) is used to flatten the array and return individual rows for each order.

When to Use RECORD Type?

• When dealing with hierarchical or structured data.
• To reduce table joins and improve query efficiency.
• When working with JSON-like or denormalized datasets.