CODEWITHSUNDEEP
sqlgoogle-bigquery
Yang
Yang

Reputation: 199

bigquery pivoting with nested field

I have a bigquery table with schema:

enter image description here

I would like to have this result:

flow_timestamp, channel_name, number_of_digits
2019-10-31 15:31:15, channel_name_1, 3,
2019-10-31 15:31:15, channel_name_2, 4,
:
:

My query: SELECT flow_timestamp, timeseries.channel_name, MAX(IF(channel_properties.key = 'number_of_digits', channel_properties.value, NULL)) AS number_of_digits FROM my_table , unnest(timeseries.channel_properties) as channel_properties

I have tried same technic shown here, How to aggregate multiple rows into one in BigQuery?

but got an error SELECT list expression references column flow_timestamp which is neither grouped nor aggregated at [1:8]

Upvotes: 0

Views: 467

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173056

Below is for BigQuery Standard SQL 

#standardSQL
SELECT 
  flow_timestamp, 
  timeseries.channel_name, 
  ( SELECT MAX(IF(channel_properties.key = 'number_of_digits', channel_properties.value, NULL)) 
    FROM UNNEST(timeseries.channel_properties) AS channel_properties
  ) AS number_of_digits 
FROM my_table

Upvotes: 1

Related Questions