如何在BigQuery中批量计算JSON对象与JSON数组列的差值？

场景说明 #

现有sandbox.test表结构如下（prediction.temp为JSON数组）：

CREATE OR REPLACE TABLE sandbox.test(
  id INT64,
  observation JSON,
  prediction JSON
);

INSERT INTO sandbox.test VALUES
(1, 
 JSON """{"temp": {"sensor": "foo", "value": 1}}""", 
 JSON """{"temp": [{"source": "alpha", "value": 3}, {"source": "beta", "value": 1}]}""");

当prediction.temp是数组时，逐个指定下标计算差值效率低下，我们可以通过以下方法批量遍历数组元素计算差值，并将结果转为JSON对象。

解决方案 #

使用BigQuery的JSON_EXTRACT_ARRAY解析数组，结合UNNEST展开元素，计算差值后再用JSON_OBJECT_AGG聚合为JSON对象：

SELECT
  id,
  observation,
  prediction,
  JSON_OBJECT_AGG(
    temp_item.source,
    FLOAT64(observation.temp.value) - FLOAT64(temp_item.value)
  ) AS delta
FROM sandbox.test,
UNNEST(JSON_EXTRACT_ARRAY(prediction.temp)) AS temp_item
GROUP BY id, observation, prediction;

结果说明 #

执行上述语句后，delta字段会返回一个JSON对象，键为数组元素的source值，值为对应差值：

{"alpha": -2, "beta": 0}

如果需要保留数组格式的差值结果，可以改用JSON_ARRAY_AGG：

SELECT
  id,
  observation,
  prediction,
  JSON_ARRAY_AGG(
    JSON_OBJECT(
      "source", temp_item.source,
      "delta", FLOAT64(observation.temp.value) - FLOAT64(temp_item.value)
    )
  ) AS delta_array
FROM sandbox.test,
UNNEST(JSON_EXTRACT_ARRAY(prediction.temp)) AS temp_item
GROUP BY id, observation, prediction;

此时delta_array的结果为：

[{"source": "alpha", "delta": -2}, {"source": "beta", "delta": 0}]

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