如何展平列名含点(.)的Repeated Fields？SQL自关联遇阻求助

Hey there, let's break down this repeated fields problem you're hitting—super common when you're first working with nested/repeated data, so don't worry!

First, let's understand why your nested FLATTEN approach isn't working: every time you FLATTEN a separate repeated field (like Online.TypeA, then Online.TypeB, then Online.TypeC), you're creating a cartesian product of all their elements. That means if TypeA has 2 values, TypeB has 3, and TypeC has 4, you'll end up with 234=24 rows per original record—way more than you need, and the values won't be paired correctly from the same original nested entry.

The Fix: Match Your Table Structure to the Right SQL Approach #

There are two common scenarios for your data structure—let's cover both:

Scenario 1: Online is a Repeated Struct (Recommended Setup)

If Online is a single repeated struct where each entry contains TypeA, TypeB, and TypeC (meaning each TypeA is inherently linked to its corresponding TypeB/TypeC in the same struct), you only need to flatten the entire Online array once, not each individual field.

Using the older FLATTEN syntax:

SELECT 
  Id, 
  flattened_online.TypeA, 
  flattened_online.TypeB, 
  flattened_online.TypeC 
FROM FLATTEN([database.kind_table1], Online) AS flattened_online

Or, using the modern, recommended UNNEST syntax (preferred over FLATTEN in most data warehouses like BigQuery):

SELECT 
  Id, 
  online.TypeA, 
  online.TypeB, 
  online.TypeC 
FROM [database.kind_table1], UNNEST(Online) AS online

This will split each Online struct into its own row, keeping TypeA, TypeB, and TypeC paired exactly as they appear in the original nested data—no cartesian mess.

Scenario 2: TypeA, TypeB, TypeC are Separate Repeated Fields

If each of Online.TypeA, Online.TypeB, Online.TypeC is an independent repeated array (and their elements are positionally matched—e.g., the 1st TypeA corresponds to the 1st TypeB and 1st TypeC), you need to use WITH OFFSET to align elements by their index:

SELECT 
  Id, 
  a_val AS TypeA, 
  b_val AS TypeB, 
  c_val AS TypeC 
FROM [database.kind_table1]
CROSS JOIN UNNEST(Online.TypeA) AS a_val WITH OFFSET AS a_idx
CROSS JOIN UNNEST(Online.TypeB) AS b_val WITH OFFSET AS b_idx
CROSS JOIN UNNEST(Online.TypeC) AS c_val WITH OFFSET AS c_idx
WHERE a_idx = b_idx AND b_idx = c_idx

The WITH OFFSET clause assigns a position number to each element in the array, and the WHERE clause ensures only elements from the same position across all three arrays are paired together.

Once you've flattened the data correctly using one of these methods, table self-joins will work as expected—you can reference the flattened fields just like any other column.

内容的提问来源于stack exchange，提问作者mike winston