如何基于字典表多行数据在BigQuery中动态创建STRUCT

一、动态生成空STRUCT结构 #

在支持动态SQL的引擎（如BigQuery、PostgreSQL）中，可通过拼接SQL字符串自动生成目标STRUCT：

1. 生成STRUCT字段片段 #

执行以下查询获取拼接好的字段内容：

SELECT STRING_AGG(CONCAT('"" AS ', field_name), ',\n  ') AS struct_fields
FROM fields;

返回结果直接嵌套进STRUCT()即可得到你需要的空STRUCT语句：

STRUCT (
  "" AS full_name,
  "" AS email,
  "" AS address,
  "" AS ip_address
)

2. 直接生成带STRUCT的动态查询 #

以BigQuery为例，用EXECUTE IMMEDIATE自动生成并执行：

DECLARE struct_sql STRING;
SET struct_sql = (
  SELECT CONCAT('STRUCT(\n  ', STRING_AGG(CONCAT('"" AS ', field_name), ',\n  '), '\n) AS struct_tx')
  FROM fields
);
EXECUTE IMMEDIATE struct_sql;

二、关联base_data填充STRUCT字段 #

你的CROSS JOIN方案没必要，直接通过动态SQL从base_data提取字段构造STRUCT更高效：

动态生成填充后的STRUCT查询 #

DECLARE fill_struct_sql STRING;
SET fill_struct_sql = (
  SELECT CONCAT(
    'SELECT id,\n  STRUCT(\n    ',
    STRING_AGG(CONCAT('base_data.', field_name, ' AS ', field_name), ',\n    '),
    '\n  ) AS target_struct\nFROM base_data'
  )
  FROM fields
);
EXECUTE IMMEDIATE fill_struct_sql;

该语句会自动读取fields表中的字段名，从base_data中提取对应值组装成STRUCT，无需手动编写字段映射。

若坚持使用CROSS JOIN（特殊场景） #

如果必须先关联空STRUCT再填充，可重新构造STRUCT完成赋值：

WITH struct_tx AS (
  SELECT STRUCT(
    "" AS full_name,
    "" AS email,
    "" AS address,
    "" AS ip_address
  ) AS empty_struct
)
SELECT
  base_data.id,
  STRUCT(
    base_data.full_name AS full_name,
    base_data.email AS email,
    base_data.address AS address,
    base_data.ip_address AS ip_address
  ) AS filled_struct
FROM base_data
CROSS JOIN struct_tx;

注意：这种方式仍需手动映射字段，仅适合字段固定的场景。

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