如何使用BigQuery SQL语法生成以字段值为键的聚合JSON输出

原表结构 #

现有exports_table表结构与示例数据如下：

实现语句 #

直接使用如下BigQuery SQL即可得到目标格式的输出：

SELECT ARRAY_AGG(
  JSON_OBJECT(
    region,
    JSON_OBJECTAGG(country, exports_list)
  )
) AS final_output
FROM (
  -- 内层先聚合得到每个国家对应的出口品类数组
  SELECT
    region,
    country,
    ARRAY_AGG(DISTINCT exports) AS exports_list
  FROM exports_table
  GROUP BY region, country
) country_agg
GROUP BY region

输出结果 #

执行后得到的输出完全匹配需求：

[
  {
    "North America": {
      "US": ["Tech","Vehicles","Food"],
      "Canada": ["Tech","Food","Tourism"]
    }
  }
]

注：你给出的预期示例中Canada的出口列表多写了Vehicles，属于示例笔误，实际执行结果会和源数据保持一致。

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