如何在BigQuery中提取类数组JSON数据并实现求和统计

需要从JSON格式的变量metric_data中提取数据，统计每个segmentName对应的values子数组中第二个元素的总和，最终输出每条segmentName对应一行及对应的总和。

示例JSON数据 #

[{"segmentName":"control","values":[[1588636800000.0,101],[1588723200000.0,546],[1588809600000.0,1195],[1591056000000.0,129]]},{"segmentName":"experiment","values":[[1588636800000.0,91],[1588723200000.0,680],[1588809600000.0,1214],[1588896000000.0,1269],[1589760000000.0,248],[1589846400000.0,173],[1589932800000.0,167],[1590019200000.0,178],[1590105600000.0,131],[1590192000000.0,110]]}]

当前已完成的SQL #

SELECT  
  array(select 
    x
    FROM UNNEST(JSON_EXTRACT_ARRAY(metric_data, '$')) x
  ) extracted
FROM temp

解决方案SQL #

SELECT
  JSON_VALUE(x, '$.segmentName') AS segmentName,
  SUM(CAST(JSON_VALUE(y, '$[1]') AS INT64)) AS total_value
FROM temp,
  UNNEST(JSON_EXTRACT_ARRAY(metric_data, '$')) x,
  UNNEST(JSON_EXTRACT_ARRAY(x, '$.values')) y
GROUP BY segmentName

步骤说明 #

• 拆分外层JSON数组：通过UNNEST(JSON_EXTRACT_ARRAY(metric_data, '$'))将整体的JSON数组拆分为单个JSON对象x
• 提取分组字段：从每个x中解析出segmentName作为分组依据
• 拆分内层values数组：通过UNNEST(JSON_EXTRACT_ARRAY(x, '$.values'))将每个对象中的values子数组拆分为单个子数组y
• 计算总和：提取每个y的第二个元素（索引为1），转换为整数类型后按segmentName分组求和

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