如何将BigQuery表数据转换为指定结构的JSON输出

我来帮你搞定这个把BigQuery表数据转换成目标嵌套JSON格式的需求！咱们直接用BigQuery的原生SQL聚合函数就能实现，不用额外写代码处理，效率拉满。

核心思路 #

我们需要分两层聚合来构建嵌套结构：

1. 先按DataProvider和Id分组，把每个Id对应的Name和Time记录打包成一个数组
2. 再按DataProvider分组，把所有Id的聚合结果打包成items数组，最后用STRUCT和TO_JSON_STRING生成目标JSON结构

具体SQL语句 #

把下面的表路径替换成你自己的BigQuery项目、数据集和表名即可：

SELECT
  TO_JSON_STRING(STRUCT(
    DataProvider AS dataProviderId,
    ARRAY_AGG(STRUCT(
      Id,
      ARRAY_AGG(STRUCT(Name, Time)) AS data
    )) AS items
  )) AS result_json
FROM
  `your-project.your-dataset.your-table`
GROUP BY
  DataProvider;

执行结果说明 #

针对你给出的三条数据，执行后会输出以下JSON（注意你给出的期望输出里第二个Name写成了xyz，实际数据里是123，所以这里是对应真实数据的正确结果）：

{"dataProviderId":"ABC","items":[{"Id":"f8453e99-516f-4f15-a3bd-8749089b6934","data":[{"Name":"xyz","Time":43200},{"Name":"123","Time":43200}]},{"Id":"00453e99-516f-4f15-a3bd-8749089b6934","data":[{"Name":"xyz","Time":43200}]}]}

导出为JSON文件（可选） #

如果需要直接把结果导出成JSON文件存储到Google Cloud Storage，可以用EXPORT DATA语句：

EXPORT DATA
OPTIONS(
  format='JSON',
  uri='gs://your-bucket/path/to/output/*.json'
) AS
SELECT
  STRUCT(
    DataProvider AS dataProviderId,
    ARRAY_AGG(STRUCT(
      Id,
      ARRAY_AGG(STRUCT(Name, Time)) AS data
    )) AS items
  ) AS result
FROM
  `your-project.your-dataset.your-table`
GROUP BY
  DataProvider;

内容的提问来源于stack exchange，提问作者Rajeev A Nair