如何用优雅的BigQuery SQL将JSON数据转换为结构化表格？

我有一个BigQuery表，仅包含一列一行数据：

需要将该JSON数据转换为如下结构化表格：

目前我使用以下SQL实现，但希望有更优雅的写法：

SELECT 'A' AS name, 
    JSON_EXTRACT(Json, "$.A.min") as min_value, 
    JSON_EXTRACT(Json, "$.A.max") as max_value
FROM table_name
UNION ALL 
SELECT 'B' AS name, 
    JSON_EXTRACT(Json, "$.B.min") as min_value, 
    JSON_EXTRACT(Json, "$.B.max") as max_value
FROM table_name
UNION ALL 
SELECT 'C' AS name, 
    JSON_EXTRACT(Json, "$.C.min") as min_value, 
    JSON_EXTRACT(Json, "$.C.max") as max_value
FROM table_name

推荐使用BigQuery的JSON_KEYS配合UNNEST函数实现动态解析，无需重复编写UNION ALL语句，代码更简洁且扩展性更强（后续JSON新增顶层键时无需修改SQL）：

SELECT
  key AS Name,
  CAST(JSON_EXTRACT_SCALAR(Json, CONCAT('$.', key, '.min')) AS INT64) AS min_value,
  CAST(JSON_EXTRACT_SCALAR(Json, CONCAT('$.', key, '.max')) AS INT64) AS max_value
FROM
  table_name,
  UNNEST(JSON_KEYS(Json)) AS key

关键函数说明 #

• JSON_KEYS(Json)：提取JSON对象的所有顶层键（即示例中的'A'、'B'、'C'）
• UNNEST(...)：将提取出的键列表拆分为多行，完成行转列的转换
• JSON_EXTRACT_SCALAR：直接提取JSON中的标量值，相比JSON_EXTRACT更高效，再通过CAST转换为整数类型（可根据实际需求调整数据类型）

注：如果你的JSON是单引号格式（非标准JSON），BigQuery仍可兼容，但建议优先使用双引号的标准JSON格式以避免潜在解析问题。

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