利用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;