BigQuery map over array then take the average

Question

I need your help with a BigQuery Query.

My current query looks like this:

SELECT 
JSON_VALUE(data, "$.twitterUsername") as twitterUsername,
JSON_VALUE(data, "$.score") as latestScore,
JSON_EXTRACT_ARRAY(data, "$.previousScores") as averageScore,
FROM `tablename` LIMIT 1000

and it yields me results in the following form:

Now as you can see, the averageScore field is an array of objects with {score: number} structure. What I would like to accomplish is the following:

• Map each array object so that {score: number} => number
• Take the average of all the numbers in the array

So for instance, if our averageScore array would look like this:

[
  {score: 5},
  {score: 7},
  {score: 12}
]

I would want it converted to (12+7+5)/3 = 8

Thanks for your help

Answer 1

I solved it myself using this command:

SELECT 
JSON_VALUE(data, "$.twitterUsername") as twitterUsername,
JSON_VALUE(data, "$.score") as latestScore,
(SELECT AVG(CAST(JSON_QUERY(x, "$.score") as INT64)) FROM UNNEST(JSON_EXTRACT_ARRAY(data, "$.previousScores")) as x) as averageScore,
FROM `tablename` LIMIT 1000