Google BigQuery - Bài học 12: Làm chủ UNNEST – Cách "trải phẳng" (Flatten) dữ liệu mảng và truy vấn siêu tốc

Chào anh em! Ở Bài học 11, chúng ta đã mở ra một chân trời mới với kiểu dữ liệu ARRAY và STRUCT. Thay vì phải xé nhỏ bảng, tạo khóa ngoại và viết các câu lệnh JOIN rườm rà qua mạng Jupiter như trong MySQL, chúng ta có thể nhốt trọn danh sách sản phẩm (items) vào ngay trong một dòng duy nhất của bảng đơn hàng.

Nhưng câu hỏi đặt ra tiếp theo là: "Bây giờ dữ liệu đã nằm gọn trong mảng rồi, nếu tôi muốn viết một câu lệnh WHERE để lọc ra tất cả những đơn hàng có chứa một sản phẩm cụ thể, hoặc muốn GROUP BY theo tên sản phẩm để tính tổng doanh thu thì phải làm sao?"

Đó chính là lúc vũ khí tối thượng mang tên UNNEST() xuất hiện! Hôm nay, chúng ta sẽ mổ xẻ tường tận cách "trải phẳng" mảng trong GoogleSQL một cách mượt mà và không sợ "bùng nổ dòng".

1. Bản chất của UNNEST() là gì?

Nhiều anh em mới làm quen với BigQuery thường hiểu nhầm rằng UNNEST() là một hàm số giống như CONCAT hay SUBSTRING. Sai hoàn toàn nhé!

Trong GoogleSQL, UNNEST() là một toán tử chuyển đổi tập hợp (Table Operator). Nhiệm vụ duy nhất của nó là nhận vào một cột kiểu ARRAY (một cục dữ liệu đang bị gói gọn trong một ô của bảng) và "trải phẳng" (flatten) nó ra thành một bảng ảo gồm nhiều dòng độc lập (Virtual Table).

Hãy tưởng tượng bạn có một chiếc hộp chứa 3 món quà (mảng 3 phần tử). UNNEST chính là động tác mở hộp, bày 3 món quà đó xếp thành một hàng ngang trên bàn để bạn có thể cầm nắm, kiểm tra từng món một.

2. Cú pháp thực chiến: CROSS JOIN UNNEST vs LEFT JOIN UNNEST

Khi bạn muốn kết hợp bảng cha (ví dụ orders) với mảng con bên trong nó (items), bạn phải "nối" chúng lại bằng từ khóa JOIN. GoogleSQL cung cấp 2 cách nối kinh điển:

2.1. CROSS JOIN UNNEST (Tương đương INNER JOIN)

Cú pháp này sẽ trải phẳng mảng items và chỉ giữ lại những đơn hàng có chứa ít nhất 1 sản phẩm. Nếu một đơn hàng nào đó vì lỗi hệ thống mà có mảng items trống ([]) hoặc NULL, đơn hàng đó sẽ bị biến mất khỏi kết quả truy vấn.

SQL

SELECT 
    o.order_id,
    o.customer_name,
    item.product_name,
    item.quantity
FROM `my-project.core_dwh.orders_nested` AS o
CROSS JOIN UNNEST(o.items) AS item;

(Lưu ý cú pháp viết tắt cực kỳ phổ biến trong cộng đồng GoogleSQL: Bạn có thể thay cụm CROSS JOIN UNNEST(...) bằng một dấu phẩy , đơn giản như sau: FROM orders_nested AS o, UNNEST(o.items) AS item).

2.2. LEFT JOIN UNNEST (Giữ lại tất cả đơn hàng)

Nếu bạn muốn lấy tất cả các đơn hàng, kể cả những đơn hàng không có sản phẩm nào trong mảng items (lúc này các trường của item sẽ trả về NULL), hãy dùng LEFT JOIN:

SQL

SELECT 
    o.order_id,
    o.customer_name,
    item.product_name
FROM `my-project.core_dwh.orders_nested` AS o
LEFT JOIN UNNEST(o.items) AS item;

3. Ví dụ thực tế: Từ mảng lồng nhau đến báo cáo doanh thu chi tiết

Hãy thử giải quyết một bài toán kinh doanh thực tế: "Tính tổng doanh thu và số lượng bán ra của từng sản phẩm dựa trên bảng đơn hàng có cấu trúc lồng nhau (ARRAY<STRUCT>)".

Với cấu trúc orders_nested từ bài trước, câu lệnh GoogleSQL của chúng ta sẽ ngắn gọn và cực kỳ sạch sẽ như sau:

SQL

SELECT 
    item.product_name,
    SUM(item.quantity) AS total_sold_quantity,
    SUM(item.quantity * item.price) AS total_revenue
FROM `my-project.core_dwh.orders_nested` AS o,
UNNEST(o.items) AS item
WHERE o.created_at >= '2026-10-01'
GROUP BY item.product_name
ORDER BY total_revenue DESC;

Điều gì xảy ra bên dưới gầm máy khi bạn chạy câu lệnh này?

1. Dremel không hề thực hiện bất kỳ phép JOIN tốn kém nào qua mạng Jupiter giữa hai bảng vật lý khác nhau.

2. Nó UNNEST trực tiếp các cột con đã được Capacitor bóc tách sẵn trên ổ đĩa Colossus.

3. Quá trình tính toán SUM và GROUP BY diễn ra song song trên hàng ngàn Slots trong chớp mắt.

4. Cạm bẫy chí mạng: "Row Explosion" (Bùng nổ dòng) và cách phòng tránh

Khi làm việc với UNNEST, có một cái bẫy tư duy cực kỳ nguy hiểm mà các lập trình viên hay mắc phải, gọi là Bùng nổ dòng (Row Explosion).

Tình huống gây họa:

Giả sử đơn hàng 1001 của bạn có 5 sản phẩm trong mảng items. Cùng lúc đó, trong một bảng khác hoặc một mảng khác của cùng đơn hàng, bạn lại lỡ tay UNNEST thêm một mảng shipping_logs chứa 4 lịch sử giao hàng.

Khi bạn dùng hai dấu phẩy liên tiếp để UNNEST hai mảng độc lập mà không có mối quan hệ trực tiếp:

SQL

-- CẢNH BÁO: ĐÂY LÀ CÂU LỆNH SAI LẦM!
SELECT * 
FROM `orders` AS o,
UNNEST(o.items) AS item,
UNNEST(o.shipping_logs) AS log;

• Hậu quả: Thay vì tạo ra 5 dòng (cho 5 sản phẩm) hoặc 4 dòng (cho 4 lịch sử), BigQuery sẽ tạo ra một Phép nhân chéo (Cartesian Product): $5 \times 4 = 20$ dòng cho duy nhất 1 đơn hàng 1001!

• Nếu bảng của bạn có 10 triệu đơn hàng, số lượng dòng bị nhân chéo lên hàng trăm triệu, dẫn đến tràn bộ nhớ (Out of Memory / Resources exceeded) và hóa đơn quét dữ liệu vọt lên kinh hoàng.

Bí quyết phòng tránh:

1. Chỉ UNNEST một mảng tại một thời điểm cho mỗi cấp độ quan hệ.

2. Nếu cần xử lý nhiều mảng độc lập, hãy sử dụng các hàm mảng cao cấp (Array Functions) như ARRAY_AGG, ARRAY_LENGTH, hoặc tách thành các CTE (WITH) riêng biệt trước khi đem đi kết hợp.

Tổng kết Bài học 12

Phép UNNEST() chính là chiếc cầu nối hoàn hảo giữa thế giới lưu trữ lồng nhau hiện đại (ARRAY/STRUCT) và thế giới phân tích phẳng truyền thống (GROUP BY/WHERE):

1. Không phải hàm số: Nó là toán tử trải phẳng tập hợp (Table Operator).

2. Linh hoạt kết hợp: Dùng CROSS JOIN UNNEST (hoặc dấu phẩy ,) để lọc và tính toán, dùng LEFT JOIN UNNEST khi muốn bảo toàn dữ liệu phía bảng cha.

3. Cẩn trọng nhân chéo: Tránh UNNEST nhiều mảng độc lập vô tội vạ để không rơi vào bẫy "bùng nổ dòng" (Row Explosion).

Nhưng cuộc sống của một Backend Developer không chỉ toàn dữ liệu sạch được thiết kế chuẩn chỉnh dạng ARRAY/STRUCT. Đa số các hệ thống Microservices hiện nay khi đẩy log hoặc payload từ Webhook về BigQuery thường ném thẳng một cục JSON nguyên bản (Raw JSON string) đầy hỗn loạn!

Làm thế nào để bóc tách, truy vấn và đánh index trên kiểu dữ liệu JSON mà không cần viết script Python xử lý trước?

Hẹn gặp lại anh em ở Bài học 13: Xử lý kiểu dữ liệu JSON nguyên bản (Native JSON) – Truy xuất, đánh index và tối ưu hóa payload từ API/NoSQL đổ về!