我正在使用JSON_EXTRACT
从BigQuery列中提取JSON数据。现在,我要提取值列表并对其运行汇总函数(如AVG
)。在http://jsonpath.curiousconcept.com/上测试JsonPath表达式.objects[*].v
成功。但是查询:
SELECT
JSON_EXTRACT(json_column, "$.id") as id,
AVG(JSON_EXTRACT(json_column, "$.objects[*].v")) as average_value
FROM [tablename]
在BigQuery上抛出 JsonPath解析错误。 在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
。 最佳答案
利用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;