sql - 如何将聚合函数应用于从 Google BigQuery 中的 JSON 提取的数据？

Question

我正在从 BigQuery 中提取 JSON 数据列使用 JSON_EXTRACT .现在我想提取值列表并对它们运行聚合函数(如 AVG )。测试 JsonPath 表达式 .objects[*].v在 http://jsonpath.curiousconcept.com/ 上成功.但是查询:

SELECT
  JSON_EXTRACT(json_column, "$.id") as id,
  AVG(JSON_EXTRACT(json_column, "$.objects[*].v")) as average_value
FROM [tablename]

抛出一个 JsonPath 解析错误 在 BigQuery 上。 这在 BigQuery 上可行吗？ 或者我是否需要预处理我的数据才能对我的 JSON 中的数据运行聚合函数？

我的数据看起来类似于:

# Record 1
{
  "id": "abc",
  "objects": [
    {
      "id": 1,
      "v": 1
    },
    {
      "id": 2,
      "v": 3
    }
  ]
}
# Record 2
{
  "id": "def",
  "objects": [
    {
      "id": 1,
      "v": 2
    },
    {
      "id": 2,
      "v": 5
    }
  ]
}

这与另一个 question有关.

更新:可以通过运行两个查询来简化问题。首先运行 JSON_EXTRACT并将结果保存到 View 中。其次，针对该 View 运行聚合函数。但即便如此，我也需要更正 JsonPath 表达式 $.objects[*].v防止 JSONPath parse error .

Answer 1

利用 SPLIT() 将可重复字段转换为单独的行。将其放入子查询并将 AVG 放在外面也可能更容易/更干净:

SELECT id, AVG(v) as average 
FROM (
SELECT 
    JSON_EXTRACT(json_column, "$.id") as id, 
    INTEGER( 
      REGEXP_EXTRACT(
        SPLIT(
          JSON_EXTRACT(json_column, "$.objects")
          ,"},{"
          )
        ,r'\"v\"\:([^,]+),')) as v FROM [mytable] 
)
GROUP BY id;