-6

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.

Mich Talebzadeh
  • 83
  • 1
  • 7
  • Just to confirm, is the value of `metrics` array of structs? Did you get the struct you provided above after doing `UNNEST`? Can you provide a complete sample data? What error do you encounter? What is your expected output data? – Ricco D May 17 '22 at 02:53
  • Hi @Ricco D yes it is a struct type. I can see the elements when I do unnest. However, i want to refer to individual element for example "metrics.source_table" in my SQL itself. The unpacked struct looks like {"metric_type":"record_value", "metric_name": "source_table", metric_value": "ABC"} Thanks – Mich Talebzadeh May 17 '22 at 04:06
  • Does it not return the expected data when you do `SELECT metrics_value.metric_type FROM my_dataset.mytable t, UNNEST(metrics) AS metrics_value`? – Ricco D May 17 '22 at 04:10
  • UI unpacks that column metrics as below Row metric_type metric_name metric_value 1 record_value source_table ABC – Mich Talebzadeh May 17 '22 at 04:14
  • some progress it returns metrics_value.metric_type but sum(metric_value.row_count) fails with "Field name row_count does not exist in STRUCT! How can I sum rows here – Mich Talebzadeh May 17 '22 at 04:26
  • If you get that error, your logic might be incorrect. It is better if you provide a complete sample data so the community can easily reproduce your issue. – Ricco D May 17 '22 at 05:01
  • thanks all. The script provided by the user had erroneous struct column unfortunately – Mich Talebzadeh May 17 '22 at 20:54
  • Does this answer your question? [SQL sum with condition](https://stackoverflow.com/questions/4517681/sql-sum-with-condition) – Atilla Arda Açıkgöz May 20 '22 at 11:11

0 Answers0