如何在BigQuery中生成列名为键、列值为列表的动态JSON？

要实现将查询结果转为列名为键、对应列所有值组成数组为值的JSON结构，同时避免硬编码列名，纯BigQuery SQL就能高效解决，无需依赖外部代码。

核心思路 #

利用BigQuery的元数据系统INFORMATION_SCHEMA动态获取目标表的所有列名，自动构建聚合语句，将每列的值收集为数组后再转换为目标JSON格式。

完整实现代码 #

DECLARE columns STRING;

-- 1. 从元数据中获取目标表的所有列名，生成ARRAY_AGG语句片段
SET columns = (
  SELECT STRING_AGG(
    FORMAT("ARRAY_AGG(`%s` RESPECT NULLS) AS `%s`", column_name, column_name),
    ", "
  )
  FROM `your-project.your-dataset.INFORMATION_SCHEMA.COLUMNS`
  WHERE table_name = 'your-target-table'
    AND table_schema = 'your-dataset'
);

-- 2. 动态执行查询，生成目标JSON
EXECUTE IMMEDIATE FORMAT("""
  SELECT TO_JSON_OBJECT(t) AS result
  FROM (
    SELECT %s
    FROM `your-project.your-dataset.your-target-table`
  ) t
""", columns);

代码说明 #

• 动态列名生成：通过INFORMATION_SCHEMA.COLUMNS获取表的所有列，用STRING_AGG和FORMAT自动拼接出每列的ARRAY_AGG聚合语句，无需手动枚举列名。
• 数组聚合：ARRAY_AGG(列名 RESPECT NULLS)会保留列中的NULL值，若不需要可换成IGNORE NULLS。
• JSON转换：TO_JSON_OBJECT(t)将聚合后的结果行直接转为JSON，自动以列名为键、数组为值。

示例输出 #

假设目标表包含FirstName和LastName列，执行后会输出类似如下的JSON：

{
  "FirstName": ["Alice", "Bob", null],
  "LastName": ["Smith", "Jones", "Brown"]
}

优势 #

• 完全无需硬编码列名，自动适配表结构变更；
• 纯BigQuery引擎内处理，比外部Java代码性能提升显著，尤其适合大数据量场景；
• 语法简洁，可直接集成到现有BigQuery工作流中。

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