BigQuery中展开结构体时如何给列加前缀生成无重名扁平表

方法1：手动指定字段别名（适合结构体字段数量少的场景 #

直接给每个结构体字段手动指定带结构体名作为前缀的别名即可避免列名重复问题，查询语句如下：

WITH data AS (
  SELECT 'Alex' AS name, 14 AS age, 'something else 1' other_field UNION ALL
  SELECT 'Bert' AS name, 14 AS age, 'something else 2' other_field UNION ALL
  SELECT 'Chiara' AS name, 13 AS age, 'something else 3' other_field
),
struct_data AS(
    SELECT (
      SELECT AS STRUCT * EXCEPT(other_field)
      FROM UNNEST([t])
      ) AS student_data,
      (
      SELECT AS STRUCT * EXCEPT(other_field)
      FROM UNNEST([t])
      ) AS duble_data
    FROM data t
)
SELECT 
  student_data.name AS student_data_name,
  student_data.age AS student_data_age,
  duble_data.name AS duble_data_name,
  duble_data.age AS duble_data_age
FROM struct_data

方法2：动态SQL自动生成查询（适合结构体字段多、不想手动编写的场景） #

如果结构体包含大量字段，手动写别名效率低，可以用BigQuery的动态SQL功能自动生成查询语句：

EXECUTE IMMEDIATE """
WITH data AS (
  SELECT 'Alex' AS name, 14 AS age, 'something else 1' other_field UNION ALL
  SELECT 'Bert' AS name, 14 AS age, 'something else 2' other_field UNION ALL
  SELECT 'Chiara' AS name, 13 AS age, 'something else 3' other_field
),
struct_data AS(
    SELECT (
      SELECT AS STRUCT * EXCEPT(other_field)
      FROM UNNEST([t])
      ) AS student_data,
      (
      SELECT AS STRUCT * EXCEPT(other_field)
      FROM UNNEST([t])
      ) AS duble_data
    FROM data t
)
SELECT """ || (
  SELECT STRING_AGG(
    struct_name || '.' || field_name || ' AS ' || struct_name || '_' || field_name, 
    ', ' ORDER BY struct_name, field_name
  )
  FROM struct_data LIMIT 1,
  UNNEST([STRUCT('student_data' AS struct_name), ('duble_data' AS struct_name)]) structs,
  UNNEST(REGEXP_EXTRACT_ALL(TO_JSON_STRING(struct_data[struct_name]), r'"(\w+)":') field_name
) || """
FROM struct_data
"""

该方案会自动提取结构体所有字段名，自动拼接带前缀的别名规则，无需手动编写每个字段的查询逻辑。 #

内容的提问来源于stack exchange，提问作者Andrey kh