BigQuery中如何正确提取JSON字段的cost值并完成求和？

原SQL返回null的核心原因 #

• 传入JSON_VALUE的是写死的字符串字面量，不是表中存储JSON数据的JSON_columns字段，函数不会读取表内实际存储的数据
• 硬编码properties[3]固定索引取值逻辑不成立：properties是动态键值对数组，不同行中cost对应的数组下标不固定，部分行甚至可能没有cost属性，固定索引会出现取值错误、漏取的问题

正确实现SQL #

SELECT
  SUM(
    CAST(JSON_VALUE(prop, '$.value.float_value') AS FLOAT64)
  ) AS total_cost
FROM
  `missions.missions_study`,
  UNNEST(JSON_QUERY_ARRAY(JSON_columns, '$.properties')) AS prop
WHERE
  JSON_VALUE(JSON_columns, '$.mission_name') = 'recycle'
  AND JSON_VALUE(prop, '$.key') = 'cost'

逻辑说明 #

• 首先通过JSON_QUERY_ARRAY提取每行JSON数据里properties对应的数组，用UNNEST将数组拆分为单条属性元素的行结构，空数组的行会自动跳过计算
• 筛选拆分后属性键key为cost的条目，不需要依赖固定数组下标，适配所有行的属性排列顺序
• 提取cost对应的值后转为FLOAT64数值类型，通过SUM聚合得到全表符合条件的cost总和
• 如果需要将缺失cost字段的行按0计入统计，可以在CAST逻辑外包裹IFNULL(..., 0)处理

内容的提问来源于stack exchange，提问作者JP-Montoya