'Using unnest in BigQuery to work out sum of the member of struct column

I have a table that has a record type column called metrics that has the following struct

{"metric_type":"record_value", "metric_name":  "source_table", metric_value": "ABC"}

I can do the following

SELECT col, col2, col3, metrics_value
FROM `my_dataset.mytable` t.
UNNEST(metrics) AS metrics_value

This displays data in BigQuery UI with metrics as {"key":"value"}. However, I need to have these columns added to SELECT statement as per below

SELECT col, col2, col3, metrics.source_table, SUM(metrics.row_count) AS row_count
FROM `dataset.table`
GROUP BY 1,2

I have looked around but cannot find an example how to do it..

appreciate any advice.



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source